How to I grab the first non-NULL value for each column, per collection of e-mails?

[Anonymous]

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:

email	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:

email	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

[antoniotiman]

Hi Roel,

See Attachment

Regards,

Antonio

[sunny_talwar · Accepted solution]

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;

[antoniotiman]

Attachment

[swuehl]

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

[sunny_talwar]

It took me a while to understand your approach, but it is a very smart way to do this.

[johnw]

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.

[johnw]

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;