Combine logic on key field in Qlikview

[#1]

Hi All,

I have a scenario as below ,

Input:

Deal_Number	CSA_ID
1001	4
1001	5
1002	4
1002	4
1003	5
1003	5
1004	4
1004	5
1005	4
1005	4

if same deal_number has csa_id 4 and 5 than Flag(new column) value would be null(''),

if same deal_number has only 4 than Falg value should be 'X',

if same deal_number has only 4 than Falg value should be 'Y'.

Expected Output:

Deal_Number	CSA_ID	Flag
1001	4
1001	5
1002	4	X
1002	4	X
1003	5	Y
1003	5	Y
1004	4
1004	5
1005	4	Y
1005	4	Y

[Clever_Anjos]

Do you want to do at script or using a graph?

[#3]

Straight table object is preferred, else script is also fine.

[Clever_Anjos]

Maybe this?

Table:

LOAD Deal_Number,

     CSA_ID

FROM

[https://community.qlik.com/thread/232057]

(html, codepage is 1252, embedded labels, table is @1);

Left join (Table)

LOAD

  Deal_Number,

  IF(

  index(Concat(CSA_ID),'4') > 0 and index(Concat(CSA_ID),'5') > 0, null(),

  if(Concat(DISTINCT CSA_ID) = '4','X',

  if(Concat(DISTINCT CSA_ID) = '5','Y','*'))) as FLAG

Resident Table

Group by Deal_Number;

[Clever_Anjos]

Or using a table

[sunny_talwar · Accepted solution]

May be this:

=If(Concat(TOTAL <Deal_Number> DISTINCT {<CSA_ID = {4, 5}>} CSA_ID, ',') = '4', 'X',

If(Concat(TOTAL <Deal_Number> DISTINCT {<CSA_ID = {4, 5}>} CSA_ID, ',') = '5', 'Y'))

[#7]

Thanks Sunny, Clever.

I will let you know the status by next Monday(09/12/16) and meantime I will test the both the scenarios.

Best Regards,

Mahesh

[#8]

Hi Sunny,

I facing issue while trying to apply your logic,

Test case:

Trade_id=9537947

1) If I select one particular Trade_id than  I'm able to see the flag value in filed IM_ROLE.

2) If I clear all then expected value is disappearing.

Please find the attached qvw and kindly assist me to fix the issue.

Thanks,

Mahesh

[sunny_talwar]

Not sure why you took TRADE_ID as an expression instead of a dimension. Can you check now

[#10]

Excellent Sunny, I really appreciate your prompt support and your logic exactly fits my requirement and you made my day easy.

Thanks for your support.

Best Regards,

Mahesh