filling nulls while joining tables

Fri, 20 Nov 2020 18:02:30 GMT

Hi,

I have a scenario where i need two join tables and populate values in the null fields.

I have an activity table as follows:

Act_Tab:

Cust_Num	Act_Num	Act_Typ	Act_TS
A999B	567	closed	08/12/2016
A999B	999	open	12/12/2015
A999B	123	hold	1/1/2016

I have an audit table as follows:

Aud_Tab:

Cust_Num	Act_Num	Audit_Typ
A999B	567	Closed_review
A999B	999	Open_review

I'm joining both the tables{Left Join (Aud_Tab)} and was expecting an output as below:

Result:

Cust_Num	Act_Num	Act_Typ	Act_TS	Audit_Typ
A999B	567	closed	08/12/2015	Closed_review
A999B	999	open	12/12/2015	Open_review
A999B	123	hold	1/1/2016	Open_review

On joining both the tables, Ideally, I have to get an Null 'Audit_Typ' for 'Act_Num = 123'. But my requirement is to pick-up the latest('Act_TS = 12/12/2015') 'Audit_Typ' for Missing 'Act_Num' from the Aud_Tab and display them on the resultant output table.

Re: filling nulls while joining tables

maxgro — Thu, 21 Jan 2016 20:08:01 GMT

you have to resident load (bold) the result of the left join ordering by Act_TS to recalculate the field

X1:

LOAD Cust_Num,

Act_Num,

Act_Typ,

Act_TS

FROM

[https://community.qlik.com/thread/200925]

(html, codepage is 1252, embedded labels, table is @1);

left join (X1)

LOAD Cust_Num,

Act_Num,

Audit_Typ

FROM

[https://community.qlik.com/thread/200925]

(html, codepage is 1252, embedded labels, table is @2);

FINAL:

NoConcatenate

LOAD Cust_Num,

Act_Num,

Act_Typ,

if(IsNull(Audit_Typ), Peek('Audit_Typ'), Audit_Typ) as Audit_Typ,

Act_TS

Resident X1

order by Act_TS;

DROP Table X1;

topic filling nulls while joining tables in QlikView

filling nulls while joining tables

Re: filling nulls while joining tables