To Load separate records for columns with multiple values

[#1]

Hi All !

Facing an issue, having multiple values in one column separated by semicolon, but I need to have separate records for individual values .

Current Data format :

Current Scenaraio
PROJECT_NAME	REQ_ID	RQ_TARGET
MMR	2055	;77;84;
MMR	2056	;55;91;98;

But I require the data in below format :

Required Scenaraio
PROJECT_NAME	REQ_ID	RQ_TARGET
MMR	2055	77
MMR	2055	84
MMR	2056	55
MMR	2056	91
MMR	2056	98

So can anyone help in resolving this issue as I am a beginner in Qlikview !

Thanks,
Pratik

[tresesco]

User SubField() like:

Load    

      *,

     SubField(RQ_TARGET, ';') as NewRQ_TARGET

From <>;

[Anonymous]

You can use subfield()-function like this:

subfield(RQ_TARGET,';') as RQ_TARGET

[ashfaq_haseeb]

Hi,

Try below

Load *,subfield(RQ_TARGET,';') as New_RQ_TARGET;

load * Inline

[

PROJECT_NAME,REQ_ID,RQ_TARGET

MMR,2055,;77;84;

MMR,2056,;55;91;98;

]

Regards

ASHFAQ

[#5]

Thanks Tresesco for the quick response !

I tried subfield, so how to omit the old records , I mean I am getting this as O/P :

PROJECT_NAME	REQ_ID	RQ_TARGET	NewRQ_TARGET
MMR	2055	;77;84;	don't want this record
MMR	2055	;77;84;	77
MMR	2055	;77;84;	84
MMR	2056	;55;91;98;	don't want this record
MMR	2056	;55;91;98;	55
MMR	2056	;55;91;98;	91
MMR	2056	;55;91;98;	98

So how can I get only 5 records in O/P ?

Thanks,

Pratik

[ashfaq_haseeb · Accepted solution]

Try Below

Temp:

Load *,subfield(RQ_TARGET,';') as New_RQ_TARGET;

load * Inline

[

PROJECT_NAME,REQ_ID,RQ_TARGET

MMR,2055,;77;84;

MMR,2056,;55;91;98;

]

;

NoConcatenate

Main:

Load

PROJECT_NAME,

REQ_ID,

New_RQ_TARGET

Resident Temp

Where len(New_RQ_TARGET)>0;

drop Table Temp;

Regards

ASHFAQ

[tresesco]

If the first character is always a ';' , you can simply purge that one and try like:

Load

          Subfield( Mid(RQ_TARGET, 2) , ';') as NewRQ_TARGET