topic Re: Data with comma in QlikView

Data with comma

yoganantha321 — Sat, 16 Nov 2024 04:41:50 GMT

Hi all,

I have two tables one table consists of only ID's as shown below:

3,4

2,4

1,3

and another table with ID and their name

ID	Fruit name
1	Apple
2	Orange
3	Pineapple
4	Papaya

But my requirement is while clicking on id 3,4 Fruitname Pineapple and Papaya must be shown.

Anybody help in this!!!!

Regards,

Yoganantha Prakash G P

Re: Data with comma

vvira1316 — Tue, 22 Jan 2019 14:00:12 GMT

It may be possible by a link table but can you please illustrate your expectation with a table output example

Re: Data with comma

yoganantha321 — Tue, 22 Jan 2019 14:17:01 GMT

ID column as in listbox:

3,4

2,4

1,3

In straight table:

While clicking ID 3,4 in the third row

The expected output must be:

ID	Fruit name
3	Pineapple
4	Papaya

Note: while clicking the third ID.

Regards,

Yoganantha Prakash G P

Re: Data with comma

sunny_talwar — Tue, 22 Jan 2019 14:25:04 GMT

Try something like this

Table:
LOAD ID,
	 SubField(ID, ',') as LinkID;
LOAD * INLINE [
    ID
    1
    2
    "3,4"
    "2,4"
    "1,3"
];

Table2:
LOAD ID as LinkID,
	 [Fruit name];
LOAD * INLINE [
    ID, Fruit name
    1, Apple
    2, Orange
    3, Pineapple
    4, Papaya
];

Re: Data with comma

vvira1316 — Tue, 22 Jan 2019 14:50:23 GMT

Here is another option

NoConcatenate
[GroupTableSingleID]:
LOAD ID as GroupID,
SubStringCount(ID, ',') as StringCount
FROM
[IDTest.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where SubStringCount(ID, ',') = 0;

NoConcatenate
[GroupTableMultipleID]:
LOAD ID as GroupID,
SubStringCount(ID, ',') as StringCount
FROM
[IDTest.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where SubStringCount(ID, ',') > 0;

NoConcatenate
[FruitTable]:
LOAD ID,
[Fruit name]
FROM
[IDTest.xlsx]
(ooxml, embedded labels, table is Sheet2);

NoConcatenate
[LinkTable]:
LOAD
GroupID,
SubField(GroupID, ',', IterNo()) as ID
Resident [GroupTableMultipleID]
While IterNo() <= StringCount+1
;
Concatenate
LOAD
GroupID,
GroupID as ID
Resident [GroupTableSingleID]
;

DROP Table [GroupTableSingleID];
DROP Table [GroupTableMultipleID];