topic Re: Get relevant values under the same key in QlikView

Get relevant values under the same key

kfir1987 — Sat, 16 Nov 2024 21:53:14 GMT

Hi,

I am facing a problem during my project. I have 2 columns:

Key (string),

SC (string)

for each key I can find few SC. I have 3 value under SC column: direct, indirect and N/A.

my question:

In case key contain 2 instances of SC, that one of them is N/A and the second one is direct or indirect then show only the indirect or direct instance.

for example:

Key SC

1 N/A

1 Indirect

2 Direct

3 N/A

3 Direct

So in the end I want to see the below table:

Key SC

1 Indirect

2 Direct

3 Direct

I want to do it in the editor.

Thanks for the help

Re: Get relevant values under the same key

agni_gold — Thu, 15 Nov 2018 07:57:17 GMT

T:
LOAD * INLINE [
Key, SC
1, N/A
1, Indirect
2, Direct
3, N/A
3, Direct
];
Join Load Key , count(SC) as SC_Count Resident T Group by Key;

Final_Set:
Load Key , SC Resident T where SC_Count=1;Concatenate
LOAD Key , SC Resident T where SC_Count<>1 and SC<>'N/A';

DROP Table T;

Re: Get relevant values under the same key

kfir1987 — Thu, 15 Nov 2018 08:45:34 GMT

Hi,
This is not good enough, this solution useful for 2,3,4 keys. I am looking for a solution for thousands of keys.
Thanks

Re: Get relevant values under the same key

agni_gold — Thu, 15 Nov 2018 12:47:59 GMT

I would request can you share some data over which you are working, so that we can provide you a perfect solution.

Re: Get relevant values under the same key

sunny_talwar — Thu, 15 Nov 2018 13:26:21 GMT

This may be another option

Table:
LOAD Key,
	 SC,
	 Match(SC, 'Direct', 'Indirect', 'N/A') as SCNum;
LOAD * INLINE [
    Key, SC
    1, N/A
    1, Indirect
    2, Direct
    3, N/A
    3, Direct
];

FinalTable:
NoConcatenate
LOAD *
Resident Table
Order By Key, SCNum;

STORE FinalTable into Temp.qvd (qvd);
DROP Table Table, FinalTable;

Table:
LOAD Key,
	 SC
FROM Temp.qvd (qvd)
Where not Exists (Key);

Re: Get relevant values under the same key

sasis551 — Fri, 16 Nov 2018 12:03:34 GMT

try this script .may be it would helpful to you

load

Key,

from location where match(SC,'Direct','Indirect');