Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
i have a crosstable need to load into qv file. i have an sameple qv and excel as per attached.
scenario:
i have a target value template non row column based type. 1st row is loc_id , 19-Jan til...19-Dec.
what i want to achieve:
in the script , i have used
Year(num#(purgeChar(Date,'"'),'00000')) & Month(num#(purgeChar(Date,'"'),'00000')) as REPORT_YEARMONTH.
But i can only get 2019Apr.
My expected result is to change it to 201904.
Please help. i think its easy for anyone of you.
Sorry. need to upload data n qvw file separaely.
Rgds
Jim
Dear all,
my sample data.
rgds,
Jim
Use
=Date(Date#(datefield, 'yyyy-MMM'), 'yyyyMM')
Hi there,
this is my whole script, i added in the script you proposed. but came out with empty data for the REPORT-YEARMONTH
crosstable_temp:
CrossTable(Date, Value)
LOAD * FROM
crosstable_data.xlsx
(ooxml, embedded labels, table is Sheet1);
crosstable_final:
load
loc_id,
Value as TGT_VALUE,
//Year(num#(purgeChar(Date,'"'),'00000')) & Month(num#(purgeChar(Date,'"'),'00000')) as REPORT_YEARMONTH
Date(Date#(Date, 'yyyy-MMM'), 'yyyyMM') as REPORT_YEARMONTH
resident crosstable_temp;
drop table crosstable_temp;