Un concatenation data

[#1]

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,

[#2]

Hi,

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

[#3]

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.

[#4]

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

Thanks,

[#5]

Can you pls check the attached file. Thanks,

[maxgro]

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;

[#7]

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

[its_anandrjs]

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

[MarcoWedel]

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

[its_anandrjs]

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

Regards