Solved: Re: Work in to 1 Excel - Qlik Community

Report Inappropriate Content · ‎2015-10-09

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

Kushal_Chawda · ‎2015-10-09

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;

View solution in original post

Kushal_Chawda · ‎2015-10-09

for 2002 why you are not considering A8?

villegasi03 · ‎2015-10-09

Exactly. I am not seeing a pattern.

Report Inappropriate Content · ‎2015-10-09

Sorry I forgot A8. The specificity of this table is that I need to preserver the last datas and put them in current year.

villegasi03 · ‎2015-10-09

I might be missing something but it looks like you are just in need of an outer join of the two fields. That would give you all of the values from column one and two. Sorry I might need to see the corrected table again but that's what its looking like,

Report Inappropriate Content · ‎2015-10-09

Outter join it's not for 2 tables? Because the first table is a file from an excel.

villegasi03 · ‎2015-10-09

that doesn't matter. You can load the one table twice and then do an outer join.

example:

load distinct

year

from ExcelFile;

outer join

load distinct

information 1

from ExcelFile;

left join

// this is if you need the other column

load distinct

information 1,

information 2

from ExcelFile;

store example into exampleQVD.qvd (qvd);

Report Inappropriate Content · ‎2015-10-09

Effectively, I have something looks like what I need, but there's still a issue. In my second table, 2000 doesn't take 2001 results. I'm trying to be simple, first year I have some results = > Second year, I need to get the first year results plus the second year => Same thing for the next: Need: First and second years plus third.

villegasi03 · ‎2015-10-09

can you repost the example table again but with the correction that you mentioned.

Report Inappropriate Content · ‎2015-10-09

Excel:

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

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

In Red data from 2000

In Blue data from 2001

In Puple data from 2002