Crosstable - how to change data 2019Apr to 201904 ?

[jim_chan]

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

[jim_chan]

Dear all, 

my sample data. 

rgds, 

Jim

[jonathandienst]

Use

=Date(Date#(datefield, 'yyyy-MMM'), 'yyyyMM')

[jim_chan]

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;