Skip to main content
Announcements
Global Transformation Awards submissions are open! SUBMIT YOUR STORY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

filling nulls while joining tables

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_NumAct_NumAct_TypAct_TS
A999B567closed08/12/2016
A999B999open12/12/2015
A999B123hold1/1/2016

I have an audit table as follows:

Aud_Tab:

Cust_NumAct_NumAudit_Typ
A999B567Closed_review
A999B999

Open_review

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

Result:

Cust_NumAct_NumAct_TypAct_TSAudit_Typ
A999B567closed08/12/2015Closed_review
A999B999open12/12/2015Open_review
A999B123hold1/1/2016Open_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.

1 Reply
maxgro
MVP

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;