- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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_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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;