Work in to 1 Excel

[#1]

Hello,

Actually I need to work on a excel. Something like that:

Excel:

Year	Information 1	Information 2
2000	A1	B1
2000	A2	B3
2000	A4	B2
2001	A6	B7
2002	A8	B9
2002	A9	B10

I would like to transform this excel into a QVD like that:

QVD:

Year	Information 1	Information 2
2000	A1	B1
2000	A2	B3
2000	A4	B2
2001	A6	B7
2001	A1	B1
2001	A2	B3
2001	A4	B2
2002	A9	B10
2002	A6	B7
2002	A1	B1
2002	A2	B3
2002	A4	B2
2002	A8	B9

"Information 1" and "Information 2" are random values.

Regards

[villegasi03]

This should work. Its roundabout way to do it but it works. Wouldn't try this if you are dealing with large datasets though.

TEMP_TABLE:
LOAD DISTINCT
YEAR
FROM

(ooxml, embedded labels, table is Sheet1);

OUTER JOIN

LOAD DISTINCT
INFO1,
YEAR AS YEAR_TEMP
FROM

(ooxml, embedded labels, table is Sheet1);


FINAL_TABLE:
LOAD DISTINCT
YEAR AS YEAR_FINAL,
INFO1 AS INFO1_FINAL
RESIDENT TEMP_TABLE
WHERE YEAR >= YEAR_TEMP;

LEFT JOIN

LOAD DISTINCT
INFO1 AS INFO1_FINAL,
INFO2 AS INFO2_FINAL
FROM

(ooxml, embedded labels, table is Sheet1);

DROP TABLE TEMP_TABLE ;

[Kushal_Chawda · Accepted solution]

try this,

Fact:

LOAD Year,

    concat( DISTINCT [Information 1]&'-'& [Information 2],',') as Info

FROM

[https://community.qlik.com/thread/186210?sr=stream]

(html, codepage is 1252, embedded labels, table is @1)

where len(trim(Year))>0

Group by Year;

New:

noconcatenate

LOAD Distinct *,

if(RowNo()=1,Info,

if(Year<>Previous(Year),Info &','& Peek('Info1'))) as Info1

Resident Fact

Order by Year ;

DROP Table Fact;

Final:

LOAD Distinct Year,

SubField(Info2,'-',1) as Information1,

SubField(Info2,'-',2) as Information2;

LOAD Year,

SubField(Info1,',') as Info2

Resident New;

DROP Table New;

[Kushal_Chawda]

are you able to resolve your issue?