Data with comma

[yoganantha321]

Hi all,

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

ID

1

2

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

[vvira1316]

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

[yoganantha321]

ID column as in listbox:

ID

1

2

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

[sunny_talwar · Accepted solution]

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
];

[vvira1316]

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];