Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Filling the nulls with dates

Hi,

I have case where there are three columns with Product,ETL,Date and the condition is if the Product has an entry of the delete it has date respective to that so that date is to be filled at the Insert and Update entries.Please find the attachment for better understanding.

Thanks, 

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Hi Sameer

Sure:

[Original Data]:
LOAD * INLINE [
Product,ETL,Date
A,Insert,
A,Update,
A,Delete,1-Jan
B,Update,
B,Delete,15-Jan
C,Insert,
C,Update,
C,Delete,10-Jan
D,Insert,
D,Update,
];

[Mapping Table]:
MAPPING
LOAD
Product,
Date
RESIDENT
[Original Data]
WHERE
ETL = 'Delete';

RENAME TABLE [Original Data];

[Final Data]:
LOAD
*
,APPLYMAP('Mapping Table',Product,Null()) AS DeletedDate
RESIDENT [Original Data];

DROP TABLE [Original Data];

Regards,

Mauritz

View solution in original post

3 Replies
Highlighted
Partner
Partner

Hi Sameer

You can use this as a Dimension in the front end: =Aggr(ONLY({<ETL = {"Delete"}>}Date),Product)

The resulting table will look like this:

Table.PNG

If you want to do this in the script then you can load the table, create a mapping table from the resident table but only loading the Product and Date where ETL = "Delete". Then you can use ApplyMap to get the value and a Null() value if nothing is found.

Hope this helps.

Regards,

Mauritz

Highlighted
Creator II
Creator II

Can you provide me with script code it would more helpful.

Many Thanks,

Highlighted
Partner
Partner

Hi Sameer

Sure:

[Original Data]:
LOAD * INLINE [
Product,ETL,Date
A,Insert,
A,Update,
A,Delete,1-Jan
B,Update,
B,Delete,15-Jan
C,Insert,
C,Update,
C,Delete,10-Jan
D,Insert,
D,Update,
];

[Mapping Table]:
MAPPING
LOAD
Product,
Date
RESIDENT
[Original Data]
WHERE
ETL = 'Delete';

RENAME TABLE [Original Data];

[Final Data]:
LOAD
*
,APPLYMAP('Mapping Table',Product,Null()) AS DeletedDate
RESIDENT [Original Data];

DROP TABLE [Original Data];

Regards,

Mauritz

View solution in original post