topic Re: Split the concatenated field in QlikView

Split the concatenated field

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have certain codes where for each record it is separated by comma.

For eg:

Record Code

Q123 R01

Q234 R01,R02

Q345 R02,D03

Q432 D01,R01,R03

Now I need to get a list of all the codes.

Like this:

R01

R02

R03

D01

When I tried Subfield in the script I was getting multiple records for each record

Like this:

Q123 R01

Q234 R01

Q234 R02 etc..

I want distinct list of Codes without duplicating the records.

Any help is appreciated. Kindly let me know if the requirement is unclear.

Thanks,

Anupama Jagan

Re: Split the concatenated field

sunny_talwar — Wed, 13 Sep 2017 08:49:56 GMT

May be with a link table... there will be some duplication, but it will be in a separate table and your fact table won't duplicate anything

Table:

LOAD * INLINE [

Record, Code

Q123, R01

Q234, "R01,R02"

Q345, "R02,D03"

Q432, "D01,R01,R03"

];

LinkTable:

LOAD DISTINCT Code,

SubField(Code, ',') as SubCode

Resident Table;

Re: Split the concatenated field

its_anandrjs — Wed, 13 Sep 2017 08:51:45 GMT

Try only subfield but you get multiple records because it is refereed to your Record field.

Load *subfield(Code,',') as NewCode;

Load * inline

[

Record, Code

Q123 , R01

Q234 , "R01,R02"

Q345 , "R02,D03"

Q432 , "D01,R01,R03"

];

Re: Split the concatenated field

YoussefBelloum — Wed, 13 Sep 2017 08:54:11 GMT

Hi Anupama,

Try this on the script,

TextBetween(Code,'',',',1) as Code on the first load and load a second table with a resident without loading "record" column if you want only the distinct list of Codes

PS: subfiels also should work.

Regards,

Youssef

Re: Split the concatenated field

its_anandrjs — Wed, 13 Sep 2017 08:54:17 GMT

After all this Output you get

Q123 , R01

Q234 , R01

Q234 , R02

Q345 , R02

Q345 , D03

Q432 , D01

Q432 , R01

Q432 , R03

Re: Split the concatenated field

Anonymous — Wed, 13 Sep 2017 09:21:56 GMT

Thanks a lot Sunny. It worked fine

Regards,

Anupama Jagan