topic Convert do date in QlikView

Convert do date

Thu, 12 Feb 2015 06:18:40 GMT

I have a table by name COSB_AGM

there are many fileds in this table which are like wkgoo1

so i want to convert this in dat formate

iam trying this way but its not working

wkgoo1 as 01-12-2014 its giving error in the script and i tried like this also

wkg001 as 01/12/2014 but to its not taking ,

what do i need to do to achieve this ?

regards

here is the script i want to achieve in date

COSB_AGM:

LOAD

[Object number_OBJNR],

[Value/Obj. Crcy_WOG001] as 01,04,2014 ,

[Value/Obj. Crcy_WOG002] as 01,05,2014 ,

[Value/Obj. Crcy_WOG003] as 01,06,2014 ,

[Value/Obj. Crcy_WOG004] as 01,07,2014,

[Value/Obj. Crcy_WOG005]as 01,08,2014 ,

[Value/Obj. Crcy_WOG006] as 01,09,2014,

[Value/Obj. Crcy_WOG007]as 01,10,2014 ,

[Value/Obj. Crcy_WOG008] as 01,11,2014,

[Value/Obj. Crcy_WOG009] as 01,12,2014,

[Value/Obj. Crcy_WOG010] as 01,01,2014,

[Value/Obj. Crcy_WOG011] as 01,02,2014,

([Value/Obj. Crcy_WOG012]+

[Value/Obj. Crcy_WOG013]+

[Value/Obj. Crcy_WOG014]+

[Value/Obj. Crcy_WOG015]+

[Value/Obj. Crcy_WOG016]) as 01,03,2014

Resident COSB;

STORE COSB_AGM into COSB_AGM.qvd ;

Re: Convert do date

prma7799 — Thu, 12 Feb 2015 06:20:14 GMT

Hi,

Can you post some sample data?

Thanks

Prashant

Re: Convert do date

PrashantSangle — Thu, 12 Feb 2015 06:53:09 GMT

Hi,

[Value/Obj. Crcy_WOG002] as 01,05,2014 ,

Here you are using , which will never work

Try with [] or ''

try like

[Value/Obj. Crcy_WOG002] as '01-05-2014' ,

[Value/Obj. Crcy_WOG002] as [01-05-2014] ,

Regards

Re: Convert do date

robert_mika — Thu, 12 Feb 2015 06:57:18 GMT

Duplicate?

Date

it maybe useful for someone trying to answer your question.

Re: Convert do date

anbu1984 — Thu, 12 Feb 2015 07:06:31 GMT

Are you trying to rename the field names?

[Value/Obj. Crcy_WOG001] as 01,04,2014

Is this [Value/Obj. Crcy_WOG001] string or date field? Do you want to change date format?

Re: Convert do date

Thu, 12 Feb 2015 07:33:42 GMT

it has numerical values and i want to convert it to date and month out of it

Re: Convert do date

Thu, 12 Feb 2015 07:43:38 GMT

Hi,

try like

[Value/Obj. Crcy_WOG002] as '01-05-2014' ,

[Value/Obj. Crcy_WOG002] as [01-05-2014] ,

Re: Convert do date

anbu1984 — Thu, 12 Feb 2015 07:44:55 GMT

Can you post sample values of [Value/Obj. Crcy_WOG001]?

Re: Convert do date

jagan — Thu, 12 Feb 2015 07:48:13 GMT

Hi,

I am not sure why you are renaming the fields in this format, instead you can format in a meaningful way like Week1, Week2, OR 2014-01, 2014-02.....

If you want to give the same name you can try giving [] like below

COSB_AGM:

LOAD

[Object number_OBJNR],

[Value/Obj. Crcy_WOG001] as [01,04,2014] ,

[Value/Obj. Crcy_WOG002] as [01,05,2014] ,

[Value/Obj. Crcy_WOG003] as [01,06,2014] ,

[Value/Obj. Crcy_WOG004] as [01,07,2014],

[Value/Obj. Crcy_WOG005]as [01,08,2014] ,

[Value/Obj. Crcy_WOG006] as [01,09,2014],

[Value/Obj. Crcy_WOG007]as [01,10,2014] ,

[Value/Obj. Crcy_WOG008] as [01,11,2014],

[Value/Obj. Crcy_WOG009] as [01,12,2014],

[Value/Obj. Crcy_WOG010] as [01,01,2014],

[Value/Obj. Crcy_WOG011] as [01,02,2014],

([Value/Obj. Crcy_WOG012]+

[Value/Obj. Crcy_WOG013]+

[Value/Obj. Crcy_WOG014]+

[Value/Obj. Crcy_WOG015]+

[Value/Obj. Crcy_WOG016]) as [01,03,2014]

Resident COSB;

Hope this helps you.

Regards,

Jagan.

Re: Convert do date

Thu, 12 Feb 2015 09:08:06 GMT

hi jagan mohan thanks for your reply ,

after doing this iam using the cross table to get month out of this date format for which iam using the cross table .

.is is possible to get month out of this after cross table ? look this is what iam doing below . there are totally two tables after which i need to inner them and get month out of this date .

Cosb_Resident:

LOAD

[Object number_OBJNR],

[Val/COArea Crcy_WKG001] as '01-04-2014' ,

[Val/COArea Crcy_WKG002] as '01-05-2014',

[Val/COArea Crcy_WKG003] as '01-06-2014',

[Val/COArea Crcy_WKG004] as '01-07-2014',

[Val/COArea Crcy_WKG005] as '01-08-2014',

[Val/COArea Crcy_WKG006] as '01-09-2014',

[Val/COArea Crcy_WKG007] as '01-10-2014',

[Val/COArea Crcy_WKG008] as '01-11-2014',

[Val/COArea Crcy_WKG009] as '01-12-2014',

[Val/COArea Crcy_WKG010] as '01-01-2014',

[Val/COArea Crcy_WKG011] as '01-02-2014',

([Val/COArea Crcy_WKG012]+[Val/COArea Crcy_WKG013]+

[Val/COArea Crcy_WKG014]+

[Val/COArea Crcy_WKG015]+

[Val/COArea Crcy_WKG016]) as '01-03-2014'

Resident COSB;

Store Cosb_Resident into Cosb_Resident.qvd;

Drop Table Cosb_Resident ;

Project_Billing:

CrossTable(month, [Project Billing Value])

LOAD [Object number_OBJNR],

Date#([01-04-2014]) as Apr,

Date#([01-05-2014]) as May ,

Date#([01-06-2014]) as jun,

Date#([01-07-2014]) as Jul,

Date#([01-08-2014]) as Aug,

Date#([01-09-2014]) as Sep,

Date#( [01-10-2014]) as Oct,

Date#([01-11-2014]) as Nov,

Date#( [01-12-2014])as Dec,

Date#([01-01-2014]) as jan,

Date#([01-02-2014]) as Feb,

Date#([01-03-2014]) as Mar

FROM

(qvd);

Re: Convert do date

jagan — Thu, 12 Feb 2015 09:18:06 GMT

Hi,

Try like this

Temp:

CrossTable(Week, Value)

LOAD

FROM Cosb_Resident.qvd;

Data:

LOAD

Month(Date) AS Month;

LOAD

Date(Date#(Week, 'DD-MM-YYYY')) AS Date

RESIDENT Temp;

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.