Creating Flag ! ?

[#1]

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 !

[Kushal_Chawda]

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;

[MK_QSL]

How Rule3 with 1C is appearing in the final table?

[#4]

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.

[MK_QSL]

I think Kush141087 has already replied using this logic...

[senpradip007]

Have a look on the attachment.

[sasiparupudi1]

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

[sasiparupudi1]

Hi

Have you managed to solve your issue?

Sasi

[#9]

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 !

[sasiparupudi1]

Hi

Please try my script and it should give you the correct result.

Sasi