Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So I'm quite new to Qlik, and I'm still figuring out what is possible, and what is not. I was thinking of scanning this table from top to bottom, keeping track, in an array, of each item found, for which holds that it is not NULL. And then storing that array in a new table, once I've scanned all rows with an equal email.
Now it turns out that there is no way of storing an array in Qlik script, which makes that quite difficult.
PROBLEM DESCRIPTION:
I'm currently having the problem where I have a table that is ordered on email (and within each email, it is again ordered on a date). The rest of the table is filled with several items, but not all fields have a value. Some field have a NULL value.
Now the goal is to create a new table, where every email, occurs exactly once, with the most recent field for each column, that is not NULL.
How would I go about achieving this?
EXAMPLE:
Given table:
created | itemA | itemB | itemC | |
---|---|---|---|---|
a.b@c.com | 5-2-2016 | NULL | B | NULL |
a.b@c.com | 4-2-2016 | NULL | C | E |
a.b@c.com | 2-1-2016 | A | D | F |
aaa@de.fr | 6-2-2016 | B | NULL | D |
aaa@de.fr | 4-2-2016 | NULL | NULL | F |
asdf@jkl.net | 31-1-2016 | A | E | NULL |
asdf@jkl.net | 30-1-2016 | C | NULL | NULL |
asdf@jkl.net | 5-1-2016 | NULL | G | I |
asdf@jkl.net | 31-12-2015 | NULL | NULL | K |
Desired result:
created | itemA | itemB | itemC | |
---|---|---|---|---|
a.b@c.com | 5-2-2016 | A | B | E |
aaa@de.fr | 6-2-2016 | B | NULL | D |
asdf@jkl.net | 31-1-2016 | A | E | I |
May be this:
Table:
LOAD email,
created,
itemA,
itemB,
itemC
FROM
[https://community.qlik.com/thread/239083]
(html, codepage is 1252, embedded labels, table is @1);
TempTable:
NoConcatenate
LOAD email,
created,
If(email = Previous(email), If(itemA <> 'NULL', itemA, Peek('itemA')), itemA) as itemA,
If(email = Previous(email), If(itemB <> 'NULL', itemB, Peek('itemB')), itemB) as itemB,
If(email = Previous(email), If(itemC <> 'NULL', itemC, Peek('itemC')), itemC) as itemC
Resident Table
Order By email, created;
FinalTable:
NoConcatenate
LOAD email,
Date(Max(created)) as created,
FirstSortedValue(itemA, -created) as itemA,
FirstSortedValue(itemB, -created) as itemB,
FirstSortedValue(itemC, -created) as itemC
Resident TempTable
Group By email;
DROP Tables Table, TempTable;
Hi Roel,
See Attachment
Regards,
Antonio
May be this:
Table:
LOAD email,
created,
itemA,
itemB,
itemC
FROM
[https://community.qlik.com/thread/239083]
(html, codepage is 1252, embedded labels, table is @1);
TempTable:
NoConcatenate
LOAD email,
created,
If(email = Previous(email), If(itemA <> 'NULL', itemA, Peek('itemA')), itemA) as itemA,
If(email = Previous(email), If(itemB <> 'NULL', itemB, Peek('itemB')), itemB) as itemB,
If(email = Previous(email), If(itemC <> 'NULL', itemC, Peek('itemC')), itemC) as itemC
Resident Table
Order By email, created;
FinalTable:
NoConcatenate
LOAD email,
Date(Max(created)) as created,
FirstSortedValue(itemA, -created) as itemA,
FirstSortedValue(itemB, -created) as itemB,
FirstSortedValue(itemC, -created) as itemC
Resident TempTable
Group By email;
DROP Tables Table, TempTable;
Attachment
I think you should be possible to do this with one table LOAD (I assume your original table does not need a NULLMAP and the Temp table):
NULLMAP:
MAPPING LOAD In, NULL() INLINE [
In
NULL
];
MAP itemA, itemB, itemC using NULLMAP;
Temp:
LOAD email,
Date#(created,'DD-MM-YYYY') as Date,
itemA,
itemB,
itemC
FROM
"https://community.qlik.com/message/1156105"
(html, codepage is 1252, embedded labels, table is @1);
NoConcatenate
LOAD email, Max(Date) as Date,
FirstSortedValue( If(len(trim(itemA)),itemA),-If(len(trim(itemA)),Date)) as itemA,
FirstSortedValue( If(len(trim(itemB)),itemB),-If(len(trim(itemB)),Date)) as itemB,
FirstSortedValue( If(len(trim(itemC)),itemC),-If(len(trim(itemC)),Date)) as itemC
Resident Temp
GROUP BY email;
DROP TABLE Temp;
Date | itemA | itemB | itemC | |
---|---|---|---|---|
5-2-2016 | a.b@c.com | A | B | E |
6-2-2016 | aaa@de.fr | B | D | |
31-1-2016 | asdf@jkl.net | A | E | I |
It took me a while to understand your approach, but it is a very smart way to do this.
Likewise, took me a little time. Not sure if nulls are really null() or 'NULL', but here's a very short script using the same idea if they're 'NULL' and we're allowed to return null(). Easily modified if the input is null().
LOAD
email
,max(created) as Date
,firstsortedvalue(itemA,if(itemA<>'NULL',-created)) as itemA
,firstsortedvalue(itemB,if(itemB<>'NULL',-created)) as itemB
,firstsortedvalue(itemC,if(itemC<>'NULL',-created)) as itemC
FROM "https://community.qlik.com/message/1156105"
(html, codepage is 1252, embedded labels, table is @1)
GROUP BY email;
Edit: No reason for preceeding load. Do in one step.
Edit: Of course this is just what Stefan was saying - combine it all into one step if the original data is set up appropriately.
Or the kind of silly:
SET first = firstsortedvalue($1,if($1<>'NULL',-created)) as $1;
LOAD email,max(created) as Date,$(first(itemA)),$(first(itemB)),$(first(itemC))
FROM "https://community.qlik.com/message/1156105"
(html, codepage is 1252, embedded labels, table is @1)
GROUP BY email;