Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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
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;
Dear Swueh,
It's working now.
Thank you very much for your support.