topic Creating Flag ! ? in QlikView

Creating Flag ! ?

Wed, 12 Aug 2015 11:12:20 GMT

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 !

Re: Creating Flag ! ?

Kushal_Chawda — Wed, 12 Aug 2015 11:19:16 GMT

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;

Re: Creating Flag ! ?

MK_QSL — Wed, 12 Aug 2015 11:19:17 GMT

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

Re: Creating Flag ! ?

Wed, 12 Aug 2015 11:21:59 GMT

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.

Re: Creating Flag ! ?

MK_QSL — Wed, 12 Aug 2015 11:22:52 GMT

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

Re: Creating Flag ! ?

senpradip007 — Wed, 12 Aug 2015 11:25:30 GMT

Have a look on the attachment.

Re: Creating Flag ! ?

sasiparupudi1 — Wed, 12 Aug 2015 13:37:16 GMT

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

Re: Creating Flag ! ?

sasiparupudi1 — Thu, 13 Aug 2015 08:37:18 GMT

Have you managed to solve your issue?

Sasi

Re: Creating Flag ! ?

Thu, 13 Aug 2015 09:32:11 GMT

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 !

Re: Creating Flag ! ?

sasiparupudi1 — Thu, 13 Aug 2015 09:39:00 GMT

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

Sasi

Re: Creating Flag ! ?

Kushal_Chawda — Thu, 13 Aug 2015 09:57:17 GMT

PriyankaSR‌ have tried the script I suggested?

Re: Creating Flag ! ?

Thu, 13 Aug 2015 15:33:06 GMT

Hi Kush,

Appreciate your reply.

Yes, this way I am able to get the Rules mentioned ine Mapping Table by Inner Join.

But somehow didn't get the part to fetch all other cases .

To be specific the Condition ' where not Exists(Rule_Set)', how can I use it on the Original Table as mentioned ?

Can you pls explain this part ?

Thanks !!

Re: Creating Flag ! ?

senpradip007 — Fri, 14 Aug 2015 05:47:02 GMT

If you reach your desire goal, close this thread by choosing correct or helpful to learn others.