Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Work in to 1 Excel

Hello,

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

Excel:

YearInformation 1Information 2

2000

A1B1
2000A2B3
2000A4B2
2001A6B7
2002A8B9
2002A9B10

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

QVD:

YearInformation 1Information 2
2000A1B1
2000A2B3
2000A4B2
2001A6B7
2001A1B1
2001A2B3
2001A4

B2

2002A9B10
2002A6B7
2002A1B1
2002A2B3
2002A4B2
2002A8B9

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

Regards

Tags (2)
1 Solution

Accepted Solutions

Re: Work in to 1 Excel

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;

12 Replies

Re: Work in to 1 Excel

for 2002 why you are not considering A8?

villegasi03
Contributor

Re: Work in to 1 Excel

Exactly. I am not seeing a pattern.

Not applicable

Re: Work in to 1 Excel

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
Contributor

Re: Work in to 1 Excel

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,

Not applicable

Re: Work in to 1 Excel

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

villegasi03
Contributor

Re: Work in to 1 Excel

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

Not applicable

Re: Work in to 1 Excel

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
Contributor

Re: Work in to 1 Excel

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

Not applicable

Re: Work in to 1 Excel

Excel:

Year

Information 1Information 2

2000

A1B1
2000A2B3
2000A4B2
2001A6B7
2002A8B9

2002

A9B10

QVD

Year

Information 1Information 2
2000A1B1
2000A2B3
2000A4B2
2001A6B7
2001A1B1
2001A2B3
2001A4

B2

2002A9B10
2002A6B7
2002A1B1
2002A2B3
2002A4B2
2002A8B9

In Red data from 2000

In Blue data from 2001

In Puple data from 2002

Community Browser