topic Re: Load Row as Column Value? in QlikView

Load Row as Column Value?

Anonymous — Thu, 02 Apr 2015 16:03:25 GMT

This is a loading question. An example of my data set is below. I want to load this to read the months at the top as 1 field called Month. That way, users can see the value of their accounts over a month span.

i.e Acct 5678 sum is 600 between Jan and Mar.

Re: Load Row as Column Value?

sunny_talwar — Thu, 02 Apr 2015 16:30:43 GMT

Use cross table

You may find this blog useful: The Crosstable Load

HTH

Best,

Sunny

Re: Load Row as Column Value?

MK_QSL — Thu, 02 Apr 2015 16:43:34 GMT

You need to use CrossTable function. Provide your sample data in excel file to provide you script code...

Re: Load Row as Column Value?

buzzy996 — Thu, 02 Apr 2015 16:54:50 GMT

try,

just load ur data and use cross table---u can play here rows as columns &vice-versa...

Re: Load Row as Column Value?

Anonymous — Thu, 02 Apr 2015 17:44:05 GMT

Manish - Cant figure out how to upload a file. Data simply looks like below ...

Re: Load Row as Column Value?

Anonymous — Thu, 02 Apr 2015 17:45:44 GMT

Sunny - This was very helpful. However, I have more than one qualifier field so I am struggling to understand how I make those qualifying fields known.

Re: Load Row as Column Value?

MK_QSL — Thu, 02 Apr 2015 17:56:35 GMT

Try below

CrossTable(Month,Value,4)

Load AC1, AC2, AC3, AC4,Jan,Feb,Mar.........and so on from your FIleName;

Re: Load Row as Column Value?

sunny_talwar — Thu, 02 Apr 2015 18:00:03 GMT

You can do something like this:

Table:

CrossTable(Month, Data, 4)

LOAD AC1,

AC2,

AC3,

AC4,

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

FROM

Community_158716.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Load Row as Column Value?

ramoncova06 — Thu, 02 Apr 2015 18:01:42 GMT

when you open the crosstable function just change you qualifier field to 4

it will give you this info

Re: Load Row as Column Value?

sunny_talwar — Thu, 02 Apr 2015 18:05:34 GMT

Did some further manipulations to get Month as Datefield instead of string:

Table:

CrossTable(Month, Data, 4)

LOAD AC1,

AC2,

AC3,

AC4,

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

FROM

Community_158716.xlsx

(ooxml, embedded labels, table is Sheet1);

Table1:

LOAD AC1,

AC2,

AC3,

AC4,

Num#(AC1&AC2&AC3&AC4, '####') as Acct,

Month(Date#(Month, 'MMM')) as Month,

Data

Resident Table;

DROP Table Table;

HTH

Best,

Sunny

Re: Load Row as Column Value?

Anonymous — Thu, 02 Apr 2015 18:11:33 GMT

Looks like this worked ! Thank you Manish