Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
(
OUTER JOIN
LOAD DISTINCT
INFO1,
YEAR AS YEAR_TEMP
FROM
(
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
(
DROP TABLE TEMP_TABLE ;
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;
are you able to resolve your issue?