topic Conversion Excel Structure in QlikView

Conversion Excel Structure

Wed, 01 Jul 2009 17:06:48 GMT

Hi,

I have an excel file with with procucts and subtypes. The structure looks like this:

Type_1 Type_2 Type_3 Jan Feb Mar Apr May
_______________________________________________________________
tx1 tt2 yy3 10 20 30 15 10
tx1 tt2 yy4 13 13 12 9 14
tx2 ac2 dd1 87 12 12 33 82

I'd like to generate a chart where I can see my sales figures sum per month.

|
|
|
|
|
|_________________________________
jan feb mar apr may jun......

Anyone an idea how I should convert the excel file?

Thanks in advance.

Elzo

Conversion Excel Structure

Wed, 01 Jul 2009 17:45:54 GMT

Hi,

Have a look at this below link.

http://community.qlik.com/forums/p/17151/67145.aspx#67145

i believe , if you could look at the above link . you`ll get some idea.

- Sridhar

Conversion Excel Structure

prieper — Wed, 01 Jul 2009 17:46:35 GMT

Good day,

would recommend to somewhat normalize the data and suggest to have a look into the CROSSTABLE-function

HTH

Peter

Conversion Excel Structure

Wed, 01 Jul 2009 17:50:05 GMT

Use CROSSTAB that will help you.

Regards,

Amit Somani

Conversion Excel Structure

Wed, 01 Jul 2009 17:53:39 GMT

Hi,

I had a similar requirement and below is what I did:

Load the data in script in such a manner that you get rid of all the columns for month and instead create one column called 'Month' which stores the month name and another column 'Value' which stores the value for that month.

example code:

T1:
Load
Type_1,
Type_2,
Type_3,
Jan as Value,
'Jan' as Month
From xyz;

Concatenate (T1)
Load
Type_1,
Type_2,
Type_3,
Feb as Value,
'Feb' as Month
From xyz;

You need to do this for each month. A bit tedious..but you will be able to show the data as desired.

Thanks

Amit