Script to Load All the Dates for the ID

[Anonymous]

Hi,

I have source as following:

ID	Date	Product
2433266	8/09/2017	P1
2433266		P2
2433266		P3
2433266		P4

I am unable to write the script to load the date as following. Please help

ID	Date	Product
2433266	8/09/2017	P1
2433266	8/09/2017	P2
2433266	8/09/2017	P3
2433266	8/09/2017	P4

[qliksus · Accepted solution]

Just add one more mapping table  like the below and make the data blank if the ID has no data

Map:
Mapping LOAD Distinct
Id,
max(Date) as Key
FROM
[Output.xlsx]
(ooxml, embedded labels, table is Sheet2)
Group by Id;

LOAD Id,     
     if( isnull(applymap('Map',Id)) ,'', Date) as Date,
     Product      
     
FROM
[Output.xlsx]
(ooxml, embedded labels, table is Sheet2, filters(
Replace(2, top, StrCnd(null))
))

;

[Anonymous]

Thanks susant ! But I modified my code with my column names, path etc., It is still filling null's for non-header rows.

Following is the code and screen shot. Could you please tell me, if I had miss anything here ?

Thank You !

Map:
Mapping LOAD Distinct
[ID],
max(Date(Date#([date],'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm')) as Key
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Group by [ID];

DateTest:
LOAD [ID], 
if( isnull(applymap('Map',[ID])) ,'', Date(Date#([date],'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm')) as Date,
Insurer as Ins,
Product as Prod
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, filters(
Replace(2, top, StrCnd(null))
));

[qliksus]

Is the dates still getting filled ? . Looks like the if condition is failing try create one more field like the below  and check what is that  condition returning or attach a sample file and data for me to check

Map:

Mapping LOAD Distinct

[ID],

max(Date(Date#([date],'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm')) as Key

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

Group by [ID];

DateTest:

LOAD [ID], 

if( isnull(applymap('Map',[ID])) ,'', Date(Date#([date],'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm')) as Date,

if( isnull(applymap('Map',[ID])) ,1,2) as test,

Insurer as Ins,

Product as Prod

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, filters(

Replace(2, top, StrCnd(null))

));

[Anonymous]

No. It was my mistake. Column number was incorrect in Replace function. All good.

Thank you Susant