topic Re: Help needed with Cross tables years and month data. in QlikView

Help needed with Cross tables years and month data.

Tue, 01 Sep 2015 08:50:51 GMT

Hi!

I know about Cross tables, but the file I have a little complex and could appreciate your help.

Here is a snapshop from Excel

In the first column we have countries/regions. after that every column is a year and a month. so 2006M04 - is April 2005. This goes from 2006 till present day.

The object in Qlikview is to make line graphs on this.

The problem i face with using Crosstable for this is that I can really put all of the 2006M04... into years or month as they include both. Ideally i would like to have both month and year to play around with later.

Would anyone know a solution for this? Could the first row be named in a better way to generate month and year perhaps!

Thanks

Yahya

Re: Help needed with Cross tables years and month data.

sunny_talwar — Tue, 01 Sep 2015 10:37:10 GMT

Convert them into a monthYear format in a resident load. May be this:

Date(Date#(PurgeChar(Date, 'M'), 'YYYYMM'), 'YYYY-MM') as Date

Re: Help needed with Cross tables years and month data.

stigchel — Tue, 01 Sep 2015 10:48:29 GMT

You can firts do a crostable load, and then use a resident load. Something like this:

Data:

Load * Inline [Geo,2006M04,2006M05,2006M06

Belgium,10,20,30

Germany,11,21,31

France,12,22,32

Spain,13,23,33

];

Tmp:

CrossTable(Time, Data,1) load * resident Data;

Load

Geo,

Left(Time,4) as Year,

Right(Time,2) as Month,

Data as Value

resident Tmp;

Drop Table Tmp;

Drop Table Data;

Re: Help needed with Cross tables years and month data.

Tue, 01 Sep 2015 11:36:32 GMT

Hi sunny, is it possible for me to somehow reach you? I think you have a really nice and simple solution. I could even give you a call. (I can't seem to find a button to drop you an email)

Re: Help needed with Cross tables years and month data.

sunny_talwar — Tue, 01 Sep 2015 11:41:53 GMT

I have followed you, once you accept it, you will be able to send me private message where I can provide you with my email id.

But I feel that, in order to get best solutions, you need to share your information with everyone and I think Piet Hein van der Stigchel‌ solution is also a good solution. It will all depend on your exact requirement.

Best,

Sunny