topic Re: Unable to convert date in date format in QlikView

Unable to convert date in date format

Wed, 30 Nov 2011 10:37:59 GMT

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;

Unable to convert date in date format

swuehl — Wed, 30 Nov 2011 11:13:13 GMT

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

Re: Unable to convert date in date format

Thu, 01 Dec 2011 01:59:27 GMT

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

Re: Unable to convert date in date format

swuehl — Thu, 01 Dec 2011 12:17:34 GMT

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;

Re: Unable to convert date in date format

Fri, 02 Dec 2011 01:49:54 GMT

Dear Swueh,

It's working now.

Thank you very much for your support.