Re: Unable to convert date in date format - Qlik Community

Report Inappropriate Content · ‎2011-11-30

Dear All,

I facing difficulties to convert date from Date column to TranxDate column retrieve from crosstable.

I had tried use Date# and MakeDate statement but can't resolve.

In this discussion together I'm attach the file image, script and excel file.

Please advise.

Scripting

Date:

LOAD @1,

@2,

@3,

@4,

Date(@5) as @5,

Date(@6) as @6,

Date(@7) as @7,

Date(@8) as @8,

Date(@9) as @9,

Date(@10) as @10,

Date(@11) as @11,

Date(@12) as @12,

Date(@13) as @13,

Date(@14) as @14,

Date(@15) as @15,

Date(@16) as @16

FROM

[..\..\..\QlikViewSupplyDemandSharedFolder\Yearly\HolticultureBudget\VBU2011.xlsx]

(ooxml, no labels, header is 7 lines, table is [Produce & Variable Cost], filters(

Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

))

Where @2='SKU Code';

//To get the excel file date

Let Date1 = peek('@5',0,num#('Date'));

Let Date2 = peek('@6',0,num#('Date'));

Let Date3 = peek('@7',0,num#('Date'));

Let Date4 = peek('@8',0,num#('Date'));

Let Date5 = peek('@9',0,num#('Date'));

Let Date6 = peek('@10',0,num#('Date'));

Let Date7 = peek('@11',0,num#('Date'));

Let Date8 = peek('@12',0,num#('Date'));

Let Date9 = peek('@13',0,num#('Date'));

Let Date10 = peek('@14',0,num#('Date'));

Let Date11 = peek('@15',0,num#('Date'));

Let Date12 = peek('@16',0,num#('Date'));

// Let Date12 = peek('@16',0,'Date');

TempBudgetReset:

LOAD

@2 as Item_code,

if(isnum(@5)=-1,@5,null()) as '$(Date1)',

if(isnum(@6)=-1,@6,null()) as '$(Date2)',

if(isnum(@7)=-1,@7,null()) as '$(Date3)',

if(isnum(@8)=-1,@8,null()) as '$(Date4)',

if(isnum(@9)=-1,@9,null()) as '$(Date5)',

if(isnum(@10)=-1,@10,null()) as '$(Date6)',

if(isnum(@11)=-1,@11,null()) as '$(Date7)',

if(isnum(@12)=-1,@12,null()) as '$(Date8)',

if(isnum(@13)=-1,@13,null()) as '$(Date9)',

if(isnum(@14)=-1,@14,null()) as '$(Date10)',

if(isnum(@15)=-1,@15,null()) as '$(Date11)',

if(isnum(@16)=-1,@16,null()) as '$(Date12)'

FROM

[..\..\..\QlikViewSupplyDemandSharedFolder\Yearly\HolticultureBudget\VBU2011.xlsx]

(ooxml, no labels, header is 8 lines, table is [Produce & Variable Cost], filters(

Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

));

TempCross:

CrossTable(Date,BudgetQty,1) //change the field name

Load

*

Resident TempBudgetReset;

BudgetReset:

Load

*,

'Budget' as Flag,

Makedate(Left(Date,4),Mid(Date,5,2),Right(Date,2)),'MM/DD/YYYY' as TranxDate

Resident TempCross;

Drop Table Date;

Drop Table TempBudgetReset;

swuehl · ‎2011-11-30

I believe your Date is only a string, not a recognized date data type with numerical representation.

You should use something like

date#(@5,'M/D/YYYY') as @5

in first load to parse the date in (date() will only format the value, you need date#() to parse it in, check out also the format codes).

Hope this helps,

Stefan

Report Inappropriate Content · ‎2011-11-30

It is imposibble to do that,

If I do embedded label for the date row follow with date#(@5,'M/D/YYYY') as @5 as your suggestion it will shown empty value, while the date is not embedded the qty value in column @5 qty will be change to date

Together is this discussion I have attached my source file for better understanding.

Requisitioner assistance,

Hairul

swuehl · ‎2011-12-01

Hairul,

I think it could work like attached modified script. The important change seems to be

Date(Date#(Date),'MM/DD/YYYY') as TranxDate

I believe the dates are read in correctly as date type, but somewhere on their way they are transformed to strings again (because of the field value to variable to field name to crosstable loaded field value transformations). Since we format the dates using date() function on the very first load, a plain date#() on the last load should be enough. See attached sample file.

As a side note, peek() function takes the table name as third parameter, so I don't think a num#('Date') is useful here, and I removed the num#() function.

Date:

LOAD @1,

@2,

@3,

@4,

Date(@5) as @5,

Date(@6) as @6,

Date(@7) as @7,

Date(@8) as @8,

Date(@9) as @9,

Date(@10) as @10,

Date(@11) as @11,

Date(@12) as @12,

Date(@13) as @13,

Date(@14) as @14,

Date(@15) as @15,

Date(@16) as @16

FROM

[.\VBU2011.xlsx]

(ooxml, no labels, header is 7 lines, table is [Produce & Variable Cost], filters(

Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

))

Where @2='SKU Code';

//To get the excel file date

Let Date1 = peek('@5',0,'Date');

Let Date2 = peek('@6',0,'Date');

Let Date3 = peek('@7',0,'Date');

Let Date4 = peek('@8',0,'Date');

Let Date5 = peek('@9',0,'Date');

Let Date6 = peek('@10',0,'Date');

Let Date7 = peek('@11',0,'Date');

Let Date8 = peek('@12',0,'Date');

Let Date9 = peek('@13',0,'Date');

Let Date10 = peek('@14',0,'Date');

Let Date11 = peek('@15',0,'Date');

Let Date12 = peek('@16',0,'Date');

// Let Date12 = peek('@16',0,'Date');

TempBudgetReset:

LOAD

@2 as Item_code,

if(isnum(@5)=-1,@5,null()) as '$(Date1)',

if(isnum(@6)=-1,@6,null()) as '$(Date2)',

if(isnum(@7)=-1,@7,null()) as '$(Date3)',

if(isnum(@8)=-1,@8,null()) as '$(Date4)',

if(isnum(@9)=-1,@9,null()) as '$(Date5)',

if(isnum(@10)=-1,@10,null()) as '$(Date6)',

if(isnum(@11)=-1,@11,null()) as '$(Date7)',

if(isnum(@12)=-1,@12,null()) as '$(Date8)',

if(isnum(@13)=-1,@13,null()) as '$(Date9)',

if(isnum(@14)=-1,@14,null()) as '$(Date10)',

if(isnum(@15)=-1,@15,null()) as '$(Date11)',

if(isnum(@16)=-1,@16,null()) as '$(Date12)'

FROM

[.\VBU2011.xlsx]

(ooxml, no labels, header is 8 lines, table is [Produce & Variable Cost], filters(

Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

));

TempCross:

CrossTable(Date,BudgetQty,1) //change the field name

Load

*

Resident TempBudgetReset;

BudgetReset:

Load

*,

'Budget' as Flag,

Date(Date#(Date),'MM/DD/YYYY') as TranxDate

Resident TempCross;

Drop Table Date;

Drop Table TempBudgetReset;

Report Inappropriate Content · ‎2011-12-01

Dear Swueh,

It's working now.

Thank you very much for your support.