topic Re: To Load separate records for columns with multiple values in QlikView

To Load separate records for columns with multiple values

Mon, 04 Aug 2014 07:03:35 GMT

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

Re: To Load separate records for columns with multiple values

tresB — Mon, 04 Aug 2014 07:07:23 GMT

User SubField() like:

Load

SubField(RQ_TARGET, ';') as NewRQ_TARGET

From <>;

Re: To Load separate records for columns with multiple values

Anonymous — Mon, 04 Aug 2014 07:07:57 GMT

You can use subfield()-function like this:

subfield(RQ_TARGET,';') as RQ_TARGET

Re: To Load separate records for columns with multiple values

ashfaq_haseeb — Mon, 04 Aug 2014 07:12:13 GMT

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

Re: To Load separate records for columns with multiple values

Mon, 04 Aug 2014 07:20:01 GMT

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

Re: To Load separate records for columns with multiple values

ashfaq_haseeb — Mon, 04 Aug 2014 07:31:42 GMT

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

Re: To Load separate records for columns with multiple values

tresB — Mon, 04 Aug 2014 08:25:40 GMT

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