topic Re: Work in to 1 Excel in QlikView

Work in to 1 Excel

Fri, 09 Oct 2015 13:52:10 GMT

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

Re: Work in to 1 Excel

Kushal_Chawda — Fri, 09 Oct 2015 13:57:38 GMT

for 2002 why you are not considering A8?

Re: Work in to 1 Excel

villegasi03 — Fri, 09 Oct 2015 14:04:10 GMT

Exactly. I am not seeing a pattern.

Re: Work in to 1 Excel

Fri, 09 Oct 2015 14:10:56 GMT

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

Re: Work in to 1 Excel

villegasi03 — Fri, 09 Oct 2015 14:13:52 GMT

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,

Re: Work in to 1 Excel

Fri, 09 Oct 2015 14:27:40 GMT

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

Re: Work in to 1 Excel

villegasi03 — Fri, 09 Oct 2015 14:35:01 GMT

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);

Re: Work in to 1 Excel

Fri, 09 Oct 2015 14:56:20 GMT

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.

Re: Work in to 1 Excel

villegasi03 — Fri, 09 Oct 2015 15:03:36 GMT

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

Re: Work in to 1 Excel

Fri, 09 Oct 2015 15:17:16 GMT

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

Re: Work in to 1 Excel

villegasi03 — Fri, 09 Oct 2015 15:58:12 GMT

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 ;

Re: Work in to 1 Excel

Kushal_Chawda — Fri, 09 Oct 2015 16:26:39 GMT

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;

Re: Work in to 1 Excel

Kushal_Chawda — Mon, 12 Oct 2015 05:31:27 GMT

are you able to resolve your issue?