How to create a flag to show specific values

[jim_chan]

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. 

[jim_chan]

hi. the screenshot!

[veidlburkhard]

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

[jim_chan]

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

[veidlburkhard]

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

[jim_chan]

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. 

[veidlburkhard]

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

[Brett_Bleess]

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

[neelamsaroha157]

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)