topic Un concatenation data in QlikView

Un concatenation data

Sat, 12 Apr 2014 18:25:55 GMT

I have input records in excel like this and wanted to achieve output as shown in below. INPUT: COUNTRY YEAR US 2005|2006|2007|2008 UK 1990|1991|1992|1993 OUTPUT: COUNTRY YEAR US 2005 US 2006 US 2007 US 2008 UK 1990 UK 1991 UK 1992 UK 1993 Looks like easy, but not able to get the correct formula. Can you pls assist me. Thanks,

Re: Un concatenation data

Sat, 12 Apr 2014 18:31:10 GMT

Hi,

Could you please attach an example to get a clear idea?

Re: Un concatenation data

Sat, 12 Apr 2014 18:43:03 GMT

Hi,

I have created an inline table for the scenario and used substring

example below:

tab1:

load * inline [

Country, Year

US,2005|2006|2007|2008

UK,1990|1991|1992|1993

];

tab2:

load Country,subfield(Year,'|') as year_new

resident tab1;

hope it helps.

Re: Re: Un concatenation data

Sat, 12 Apr 2014 18:44:09 GMT

Thanks for the quick reply, pls find the attached file with tabs of INPUT and OUTPUT.

Thanks,

Re: Re: Un concatenation data

Sat, 12 Apr 2014 18:48:58 GMT

Can you pls check the attached file. Thanks,

Re: Re: Re: Un concatenation data

maxgro — Sat, 12 Apr 2014 19:19:00 GMT

RESULT

SCRIPT

Directory;

source:

LOAD COUNTRY,

STR

FROM

[Sample file for test.xlsx]

(ooxml, embedded labels, table is INPUT);

table:

NoConcatenate

load

COUNTRY,

SubField(STR, '|', 1) as YEAR,

SubField(STR, '|', 2) as BRAND,

SubField(STR, '|', 3) as Model,

SubField(STR, '|', 4) as Condition,

SubField(STR, '|', 5) as Delivered

Where

len(trim(STR)) > 0

;

load

COUNTRY,

subfield(STR, chr(126)&chr(126)) as STR

Resident

source;

DROP Tables source;

Re: Un concatenation data

Sat, 12 Apr 2014 19:39:19 GMT

tab1:

LOAD COUNTRY,

STR

FROM

(ooxml, embedded labels, table is INPUT);

tab2:

LOAD COUNTRY,

subfield(STR,'~~') as str1

FROM

(ooxml, embedded labels, table is INPUT);

tab2:

load COUNTRY,

right(subfield(str1,'|',1),4) as YEAR,

subfield(str1,'|',2) as BRAND,

subfield(str1,'|',3) as Model,

subfield(str1,'|',4) as condition,

subfield(str1,'|',5) as Delivered

Resident tab2;

hope this script helps

Re: Un concatenation data

its_anandrjs — Sat, 12 Apr 2014 20:30:35 GMT

You have to use the Subfield() function to eliminate the ~~ and | symbols from the given data please check the load script for the transformation.

SourceData:

LOAD COUNTRY,

Subfield(STR,'~~') as STR

FROM

(ooxml, embedded labels, table is INPUT);

Data:

LOAD COUNTRY,

STR,

Subfield(STR,'|',1) as YEAR,

Subfield(STR,'|',2) as BRAND,

Subfield(STR,'|',3) as Model,

Subfield(STR,'|',4) as condition,

Subfield(STR,'|',5) as Delivered

Resident SourceData

Where Len(STR)>0;

DROP Table SourceData;

See the attached final report

Regards

Re: Un concatenation data

MarcoWedel — Sat, 12 Apr 2014 23:23:10 GMT

Hi Madu,

another solution could be:

FieldMap:
Mapping LOAD * Inline [
0, <none>
1, COUNTRY
2, YEAR
3, BRAND
4, Model
5, Contidion
6, Delivered
];

tabOutput:
Generic LOAD
  %RecordID,
  ApplyMap('FieldMap', IterNo()),
  SubField(SubSTR, '|', IterNo())
While IterNo() <= SubStringCount(SubSTR, '|')+1;
LOAD
  AutoNumberHash128(COUNTR, SubSTR) as %RecordID,
  COUNTR&'|'&SubSTR as SubSTR
Where Len(SubSTR)>0;
LOAD COUNTRY as COUNTR, SubField(STR, '~~') as SubSTR
FROM [http://community.qlik.com/servlet/JiveServlet/download/507618-101744/Sample%20file%20for%20test.xlsx]
(ooxml, embedded labels, table is INPUT);

hope this helps

regards

Marco

Re: Un concatenation data

its_anandrjs — Tue, 15 Apr 2014 21:39:25 GMT

Is your solution solve or you check my solution script if so then close the thread by mark this as Correct / Helpful.

Regards

Re: Un concatenation data

MarcoWedel — Thu, 24 Apr 2014 08:11:49 GMT

Please close this thread if your problem is solved.

thanks

regards

Marco

Re: Un concatenation data

preminqlik — Thu, 24 Apr 2014 09:15:21 GMT

LOAD COUNTRY,

subfield(trim(PurgeChar(STR_NEW,'~~')),'|',1) as Year,

subfield(trim(PurgeChar(STR_NEW,'~~')),'|',2) as Brand,

subfield(trim(PurgeChar(STR_NEW,'~~')),'|',3) as Model,

subfield(trim(PurgeChar(STR_NEW,'~~')),'|',4) as Condition,

subfield(trim(PurgeChar(STR_NEW,'~~')),'|',5) as Delivered

where len(STR_NEW)>0;

LOAD *,subfield(STR,'~~') as STR_NEW INLINE [

COUNTRY, STR

US, ~~2005|SONY|MOD1|WORKING PIECE|YES ~~2006|SONY|MOD2|DAMAGE|NO ~~2007|SONY|MOD3|WORKING PIECE|NO

];