topic Re: How to create a flag to show specific values in QlikView

How to create a flag to show specific values

jim_chan — Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

I have a question here. Please have a look on my screenshot and i have also attached a test.qvw. and hopefully u guys can help me.

1) I have 2 tables - one called ABC, the other one called NON-ABC.

2) I need to create a flag that when i select ABC in the flag, it will show me ALL IDs. then when i select NON-ABC, it will show me 10001 and 10002.

Re: How to create a flag to show specific values

jim_chan — Thu, 12 Dec 2019 14:36:12 GMT

hi. the screenshot!

Re: How to create a flag to show specific values

veidlburkhard — Thu, 12 Dec 2019 17:37:39 GMT

Hi Jim_Chan,

this is very simple.

in script you load a data island 'Flag_ABC':

T1:LOAD * INLINE [
Flag_ABC
ABC
NON-ABC
];

T2:
LOAD * INLINE [
ABC_ID, PRODUCT
10001, qwerty
10002, asdfg
10003, zxcvb
10004, yuiop
];

T3:
LOAD * INLINE [
CDE_ID, PRODUCT_QA, SCORE
10001, qwerty, 100
10002, asdfg, 200
10006, zxcvb, 300
10005, yuiop, 400
];

In your front end you create a list box 'Flag_ABC' and mark 'Always one selected Value'.

Then create a text box with this expression:

=If(Flag_ABC = 'ABC', Concat(DISTINCT ABC_ID, chr(10)), Concat({1<CDE_ID = {10001, 10002}>}CDE_ID, chr(10)))

See the picture:

Hope this helps.

Burkhard

Re: How to create a flag to show specific values

jim_chan — Thu, 12 Dec 2019 23:16:29 GMT

Thanks, the 10001 and 10002 are just sample value. i cannot hardcode it in real scenario, as there are 10000 over IDs...

anyway to script it, dynamically?

Rgds

Jim

Re: How to create a flag to show specific values

veidlburkhard — Fri, 13 Dec 2019 13:14:54 GMT

Hi Jim Chan,

please tell me why 10001 and 10002 is your result in the above example.

If I would know the reason, I could tell you the way to make it dynamic.

regards

Burkhard

Re: How to create a flag to show specific values

jim_chan — Fri, 13 Dec 2019 15:13:43 GMT

10001 and 10002 it's just an example. The 2 tables that I am having now, there are 10k over same ID. Those same ID has to be flagged out. 1 of the business requirement is to flag out if same ID happen to be in the 2 tables.

Re: How to create a flag to show specific values

veidlburkhard — Mon, 16 Dec 2019 09:06:14 GMT

Hi Jim_Chan,

in order to make your selection dynamic you have to add this to your script:

T1:LOAD * INLINE [
Flag_ABC
ABC
NON-ABC
];

T2:
LOAD * INLINE [
ABC_ID, PRODUCT
10001, qwerty
10002, asdfg
10003, zxcvb
10004, yuiop
];

T3:
LOAD * INLINE [
CDE_ID, PRODUCT_QA, SCORE
10001, qwerty, 100
10002, asdfg, 200
10006, zxcvb, 300
10005, yuiop, 400
];

T4:
LOAD ABC_ID as CDE_ID
Resident T2;
LOAD CDE_ID
Resident T3;

T5:
LOAD CDE_ID,
1 as Count
Resident T4;

DROP Table T4;

Left Join(T3)
LOAD CDE_ID,
If(Sum(Count) > 1, 1, 0) as NON_ABC
Resident T5
Group By CDE_ID;

DROP Table T5;

In the front end it looks like this:

Hope this is ok for you.

Burkhard

Re: How to create a flag to show specific values

Brett_Bleess — Wed, 18 Dec 2019 20:09:25 GMT

Jim, did Burkhard's last post get you a working solution? If so, be sure you return to the post and use the Accept as Solution button on that post from him to give him credit for the assistance and to let other Community Members know that worked for this use case. If you are still working on things, leave an update with what you still need.

Regards,
Brett

Re: How to create a flag to show specific values

neelamsaroha157 — Wed, 18 Dec 2019 20:51:01 GMT

Try this -

ABC:
LOAD * INLINE [
ABC_ID, PRODUCT
10001, qwerty
10002, asdfg
10003, zxcvb
10004, yuiop
];

NON_ABC:
LOAD * INLINE [
CDE_ID, PRODUCT_QA, SCORE
10001, qwerty, 100
10002, asdfg, 200
10006, zxcvb, 300
10005, yuiop, 400
];

Left Join(NON_ABC)
LOAD CDE_ID, 1 as CommonIDFlag Resident NON_ABC where Exists(ABC_ID, CDE_ID)