topic Re: Looking for Multiple Values in QlikView

Looking for Multiple Values

Saro_2306 — Mon, 14 Jun 2021 14:46:21 GMT

Hi All,

Consider below table,

Input:

A, B

1,Area

1,Club

2,District

3,Country

3,Street

Output:

While looking for Values 1 - Area, Club 2 - District, 3 - Country, Street as like i need multiple values as output.

Anybody can help. Thanks in Advance.

Re: Looking for Multiple Values

JuanGerardo — Mon, 14 Jun 2021 17:55:04 GMT

Hi @Saro_2306, what about the Concatenate function?

Load A, A & ' - ' & Concatenate(B, ',') AS MyOutput Resident Input Group By A;

Re: Looking for Multiple Values

sasikanth — Mon, 14 Jun 2021 17:56:08 GMT

HI,

Try below code

Input:
load * Inline [
A, B

1,Area

1,Club

2,District

3,Country

3,Street
];

Output:

Load A, Concat(B,',') as place
Resident Input Group by A;

drop Table Input;

Re: Looking for Multiple Values

Saro_2306 — Mon, 14 Jun 2021 18:21:46 GMT

I think you guys are misunderstood my question. I need to lookup values. Apply map function gives 1st value as output. But i need show all possible values. Thats is what i represent as 1 - Area and Club.

Re: Looking for Multiple Values

JuanGerardo — Mon, 14 Jun 2021 18:36:12 GMT

Have you tried the Concatenate function, this is what it does, it concatenates Area and Club in the same result.

Re: Looking for Multiple Values

Saro_2306 — Mon, 14 Jun 2021 18:57:50 GMT

My actual file contains 2 table. Table 1 having data like table Input. Table 2 having unique values. (I.e 1, 2,3). In table 2 i need to show output values, as like i mentioned above, in separate column.

Column 1 | Column 2

1 | Area, Club

2| District

3| Country, Street.

Will you script gives my output.

Re: Looking for Multiple Values

sasikanth — Tue, 15 Jun 2021 03:02:15 GMT

Input:
load * Inline [
Col1, place

1,Area

1,Club

2,District

3,Country

3,Street
];

TAB2:
Load * Inline [
Col1
1
2
3
];

Output:

mapping Load Col1, Concat(place,',') as place
Resident Input Group by Col1;

drop Table Input;

FINAL:
Load Col1, ApplyMap('Output',Col1,'N/A') as Col2 Resident TAB2;

Drop Table TAB2;

Re: Looking for Multiple Values

Saro_2306 — Fri, 16 Jul 2021 13:40:32 GMT

@sasikanth

My Source having Data like this

1 - Area / Country

2 - District

3 - Country / Street.

And My Output should be like below.

1 - Area,

1 - Club

2 - District

3 - Country

3 - Street.

How to Change Script. Can you help me. Thanks in advance.

Re: Looking for Multiple Values

sasikanth — Mon, 26 Jul 2021 05:46:34 GMT

HI @Saro_2306

Try Below Script

LOAD SubField(PLACE,'-',1) &'-'& SubField(SubField(PLACE,'-',2),'/') as PLACE;
Load * Inline [
PLACE
1 - Area / Club

2 - District

3 - Country / Street.

];

EXIT SCRIPT;

(note: You may need to make necessary modifications to the script according to format).

Re: Looking for Multiple Values

Saro_2306 — Tue, 03 Aug 2021 08:23:01 GMT

Sorry bro, It is not working.

Re: Looking for Multiple Values

sasikanth — Tue, 03 Aug 2021 09:23:08 GMT

possible to share some sample data (exact format) ??

Re: Looking for Multiple Values

JuanGerardo — Thu, 26 Aug 2021 17:38:22 GMT

Hello,

The solution to this problem is the Concat() function. An example:

Input: Load * Inline [ A, B 1,Area 1,Club 2,District 3,Country 3,Street ]; Output: Load A, Concat(B, ',') AS ConcatenatedB Resident Input Group By A; Drop Table Input;

The Output table is: