Impromptu Help

[debdas]

Hi Experts,

I am a new user. My current version is 6.0 (Impromptu)
My question is, my erp stores date in long integer (20020101) ansi format and I want to convert it into week names as Sunday,Monday ....

I have tried dayname(number-to-date(trandt)) but it is not working.

Pl help

Debabrata Das

 

[griffindm]

debdas,

What is your database platform?

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[debdas]

My database is in DB2.

Debabrata Das

 

[griffindm]

debdas,

Here is a Cognos knowledgebase article that may address your needs:

KB: 85692
Replaces:


Description:

How can I convert a Julian 7 digit date to a normal date? Using AS/400, the "date" field is numeric and displays in Impromptu as 100104. We want to see 00/01/04.


Solution:

Convert the numeric value to string, substring it to remove the first character, and then use that value with the make-datetime function, converting it to integer. Substring it to obtain the corresponding values for the make-datetime arguments.

Create 2 calculations:
"Text date" = substring ( number-to-string ( Date Stamp ) , 2 , 6 )

"Final date" = make-datetime ( string-to-integer ( substring ( Text date , 1 , 2 ) ) ,string-to-integer ( substring ( Text date , 3 , 2 ) ) ,string-to-integer ( substring ( Text date , 5 , 2 ) ) )


Hope this helps,

Dave Griffin

The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[BruceReed]

Imprumtu does not do this directly, but the function "dayofweek" returns a number from 1 to 7. 1=Monday, 2=Tuesday etc. Using this & taking into actual day names would go like this:

DayNum=dayofweek(transdate)

if(daynum=1,"Monday&quot else if (daynum=2,"Tuesday"....

Hope this helps
Bruce Reed

 

[griffindm]

debdas,

The method I posted earlier does not go all the way to giving you the day name, but with the date converted to a recognizable date form, the other conversion, either at the database level or via impromtpu client functions will take you the rest of the way.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[sharatkb]

Hi debdas,

This could be a solution to ur problem...
dayname(to_date(number-to-string(20020101),'YYYYMMDD'))

 

[debdas]

No Boss, It didn't work moreover I am also not able to get to_date function

Debabrata Das

 

[debdas]

Hi Experts,

One strange thing that I am able to use functions
add-days,add-months,last-of-month or add-years but dayname,dayofweek, dayofyear not working

Is it a bug in Impromptu ???

Debabrata Das

 

[BruceReed]

As Dave hinted at, some functions work at the database level & some work at the impromtu level. It depends on the database that you are using as the source. Obviously my database (Progress via OBDC) likes dayofweek.

The solution that sharatkb & I posted will not work in your case, you will have to follow Dave's logic.

BR