topic Re: Script to Load All the Dates for the ID in QlikView

Script to Load All the Dates for the ID

Anonymous — Thu, 21 Sep 2017 03:43:51 GMT

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

Re: Script to Load All the Dates for the ID

Aurelien_Martinez — Thu, 21 Sep 2017 13:18:15 GMT

LOAD

ID,

If(Len(Date)=0, Peek(Date), Date) as Date,

Product

INLINE [

ID, Date, Product

2433266, 8/09/2017, P1

2433266, , P2

2433266, , P3

2433266, , P4

2433267, 15/09/2017, P1

2433267, , P2

2433267, , P3

2433267, , P4

];

Re: Script to Load All the Dates for the ID

antoniotiman — Thu, 21 Sep 2017 13:19:07 GMT

Hi,

may be like this

LOAD ID,Product,
If(ID=Previous(ID),Peek(Date),Date) as Date Inline [
ID, Date, Product
2433266, 8/09/2017, P1
2433266,, P2
2433266,, P3
2433266,, P4];

Regards,

Antonio

Re: Script to Load All the Dates for the ID

Anonymous — Thu, 21 Sep 2017 23:54:23 GMT

Thanks for the reply Martinez !

But I have several ID's to be loaded along with the other columns in the table.

Is there an alternative solution ?

Cheers.

Re: Script to Load All the Dates for the ID

Anonymous — Fri, 22 Sep 2017 04:32:37 GMT

Thank you for your reply !

My data is in an excel file and I have several ID's to be loaded with the dates.

I just did some research and found out that INLINE cannot be used to load from file ?

If so any other alternative ?

Re: Script to Load All the Dates for the ID

antoniotiman — Fri, 22 Sep 2017 15:20:51 GMT

Change Inline [...] to From File Excel

or provide Your source File.

Re: Script to Load All the Dates for the ID

Aurelien_Martinez — Fri, 22 Sep 2017 15:24:06 GMT

You can do that with other sources, like :

LOAD

ID,

If(Len(Date)=0, Peek(Date), Date) as Date,

Product

;

Load

from c:\MyXL.xls (biff, embedded labels, table is Sheet1$)

;

But pay attention of the order by (I think ID, Date)

Re: Script to Load All the Dates for the ID

Anonymous — Wed, 27 Sep 2017 02:00:29 GMT

Load
[ID],
If(Len(date)=0, Peek(date), date) as DateTest,
Product as ProductType;
Load *
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
where Match([type],'x','y','z');

Tried above code, it is loading just like old times. Am I Missing anything here ? Note: My csv file have more than 30 columns.

Could you please help me ?

Re: Script to Load All the Dates for the ID

qliksus — Wed, 27 Sep 2017 04:38:41 GMT

May be just use the enable transformation to achieve this . click on the fill tab and click the cell condition and click ok

as shown in the below screen

You will get the data as below

Re: Script to Load All the Dates for the ID

Anonymous — Wed, 27 Sep 2017 07:08:33 GMT

Thank you very much susant !

This option fills all the cells, even the ID's without dates.

How do we avoid those ID's, which have Date's empty ?

Re: Script to Load All the Dates for the ID

qliksus — Fri, 29 Sep 2017 05:20:07 GMT

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))
))

;

Re: Script to Load All the Dates for the ID

Anonymous — Tue, 03 Oct 2017 03:49:33 GMT

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))
));

Re: Script to Load All the Dates for the ID

qliksus — Tue, 03 Oct 2017 09:47:41 GMT

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))

));

Re: Script to Load All the Dates for the ID

Anonymous — Wed, 04 Oct 2017 03:06:38 GMT

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

Thank you Susant