Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Unable to convert date in date format

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.

1.jpg

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;

4 Replies
MVP
MVP

Unable to convert date in date format

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

Not applicable

Re: Unable to convert date in date format

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

MVP
MVP

Re: Unable to convert date in date format

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;

Not applicable

Re: Unable to convert date in date format

Dear Swueh,

It's working now.

Thank you very much for your support.

Community Browser