How to Map each value with other Values

[Vinod]

Hi all

Source Table:

REG NO	VALUE
A1	20171103-E0023-0 20171110-E0023-0
B1	20161125-E0034-0 20171130-30022-0
C1	20170717-C0036-0 20180226-E0052-0
D1	20160510-E0049-0 20160519-C0043-0 20160527-E0056-0
E1	20170330-E0059-0 20170824-A0018-0 20170922-30011-0 20171025-E0039-0

Above I have created one table and in that tables i want to Map the REG NO with each value of the VALUE column. Means  I want output like below mentioned table.        

REG  NO	VALUE
A1	20171103-E0023-0
A1	20171110-E0023-0
B1	20161125-E0034-0
B1	20171130-30022-0
C1	20160510-E0049-0
C1	20160519-C0043-0
C1	20160527-E0056-0
D1	20160510-E0049-0
D1	20160519-C0043-0
D1	20160527-E0056-0
E1	20170330-E0059-0
E1	20170824-A0018-0
E1	20170922-30011-0
E1	20171025-E0039-0

Please anyone reply     

Regards,

Vinod              

[balabhaskarqlik]

Use SubField(), to split the data based on delimiter.

Region:

Load

[Reg No],

Value

from Region.csv;

NoConcatenate

Temp:

Load

    Subfield([Reg No],' ') as [Reg No],

    Value

Resident Region;

Drop table Region;

[vishsaggi]

‌canyou share your excel file if possible pls to look into. Using advanced tab you can attch the file.

[Vinod]

Sorry sir, I am unable to find attaching link here.

[vishsaggi]

You can upload your excel file by clicking on reply to one of my messages then on right top corner you can see use advanced editor like below:

Then once you click on that a new page will open then you can see attach icon on bottom right corner. like below:

[Vinod]

In my screen it is not showing. Sir.

[Anil_Babu_Samineni]

Try to share excel file? Whether data store in cell or row?

[pooja_prabhu_n]

Hi,

can you share sample data?

You can share your excel file using Advanced editor.

[tamilarasu]

Hi Vinod,

I believe that you know how to load the data from excel to QlikView. If so, try this,

Data:
LOAD
[REG NO],
SubField(VALUE, CHR(10)) as VALUE
From Excel;

Here, Excel is your file path.

[thannila]

Try this below code:

Load

"REG NO",

SUBFIELD(replace(VALUE,left(purgechar(VALUE,'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-'),1),','),',') AS RESULT

RESIDENT Table;

[Vinod]

Hi Tamil Arasu sir, It's working.

Thank you.