Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Script to Load All the Dates for the ID

Hi,

I have source as following:

   

IDDateProduct
24332668/09/2017P1
2433266 P2
2433266 P3
2433266 P4

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

   

IDDateProduct
24332668/09/2017P1
24332668/09/2017P2
24332668/09/2017P3
24332668/09/2017P4
1 Solution

Accepted Solutions
qliksus
Specialist II
Specialist II

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

;

View solution in original post

13 Replies
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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

];

Help users find answers! Don't forget to mark a solution that worked for you!
antoniotiman
Master III
Master III

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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 ?

antoniotiman
Master III
Master III

Change Inline [...] to From File Excel

or provide Your source File.

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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)

Help users find answers! Don't forget to mark a solution that worked for you!
Anonymous
Not applicable
Author

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 ?

qliksus
Specialist II
Specialist II

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

Anonymous
Not applicable
Author

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 ?