Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a scenario wherein, I have Rule_Set, ID_no, Sys_Key.
Rule_Set | ID_no | Sys_Key |
---|---|---|
Rule1 | 1234 | 1A |
Rule1 | 2345 | 1B |
Rule2 | 3456 | 1A |
Rule3 | 4567 | 1C |
There is another Table (a Mapping Load) which has Certain Rule_Set and Sys_Key Defined. like;
Rule_Set | Sys_Key |
---|---|
Rule1 | 1A |
Rule2 | 1A |
The requirement is , the ID_no should be fetched , for the Rules mentioned in the Mapping Load Table for the Sys_Key, If the rule is not mentioned, then all the Sys_Key should be fetched. thus the result should be;
Rule_Set | ID_no | Sys_Key |
---|---|---|
Rule1 | 1234 | 1A |
Rule2 | 3456 | 1A |
Rule3 | 4567 | 1C |
The actual app has a complex data, hence Association is not the solution.
I was thinking of creating the Flag , but it somehow is feting the 'ID_no 3456' in this case for the condition where I am trying to get the rules not mentioned in the Mapping Table.
Appreciate any help!
Thanks !
Data:
LOAD Rule_Set,
ID_no,
Sys_Key
FROM
[https://community.qlik.com/thread/176269]
(html, codepage is 1252, embedded labels, table is @1);
Inner Join
LOAD Rule_Set,
Sys_Key
FROM
[https://community.qlik.com/thread/176269]
(html, codepage is 1252, embedded labels, table is @2);
Final:
NoConcatenate
LOAD Rule_Set,
ID_no,
Sys_Key
FROM
[https://community.qlik.com/thread/176269]
(html, codepage is 1252, embedded labels, table is @1)
where not Exists(Rule_Set);
Concatenate
LOAD * Resident Data;
DROP Table Data;
How Rule3 with 1C is appearing in the final table?
Hi Manish,
The condition is that, If a Rule is not mentioned, then the corresponding ID_no should be fetched, Hence Rule3 is in the resultant table.
Thanks.
I think Kush141087 has already replied using this logic...
Have a look on the attachment.
This?
load * Inline
[
Rule_Set,ID_no,Sys_Key
Rule1,1234,1A
Rule1,2345,1B
Rule2,3456,1A
Rule3,4567,1C
];
Map1:
mapping LOAD * Inline
[
Rule_Set,Sys_Key
Rule1,1A
Rule2,1A
];
NoConcatenate
Data1:
LOAD Rule_Set,ID_no,Sys_Key,if(ApplyMap('Map1',Rule_Set,'NA')='NA',1,if(ApplyMap('Map1',Rule_Set)=Sys_Key,1,0)) as IsMapAvailable
Resident Data;
NoConcatenate
LOAD Rule_Set,ID_no,Sys_Key,IsMapAvailable
Resident Data1 where IsMapAvailable=1;
drop Table Data;
drop Table Data1;
hth
Sasi
Hi
Have you managed to solve your issue?
Sasi
Hi Sasi,
Thanks for the solution option.
I have tried using the ApplyMap technique this way, It creates a flag for the Mentioned rules, but while taking up the Rule not mentioned (in this scenario Rule 3 - ID -4567), it takes up the remaining Rule as well ( here,
Rule 1- ID 2345 ) !
Appreciate any further help.
Thanks !
Hi
Please try my script and it should give you the correct result.
Sasi