topic Re: Transpose in QlikView

Transpose

Thu, 12 Dec 2013 14:43:48 GMT

Hi All

I have large piece of data in the format (see below). I am looking to transpose in the format (see below)

		01/04/2013		01/05/2013		01/06/2013
Code	Name	Num	Denom	Num	Denom	Num	Denom
NY	New York	20	3	30	6	40	3
LON	London	21	4	31	7	41	4
BIR	Birmingham	22	5	32	8	42	5
CAR	Cardiff	23	6	33	9	43	6
MAN	Manchester	24	7	34	5	44	7

After Transpose:(to look like)

Code	Name	Month	Num	Denom
NY	New York	01/04/2013	20	3
NY	New York	01/05/2013	30	6
NY	New York	01/06/2013	40	3
LON	London	01/04/2013	21	4
LON	London	01/05/2013	31	7
LON	London	01/06/2013	41	4
BIR	Birmingham	01/04/2013	22	5
BIR	Birmingham	01/05/2013	32	8
BIR	Birmingham	01/06/2013	42	5
CAR	Cardiff	01/04/2013	23	6
CAR	Cardiff	01/05/2013	33	9
CAR	Cardiff	01/06/2013	43	6
MAN	Manchester	01/04/2013	24	7
MAN	Manchester	01/05/2013	34	5
MAN	Manchester	01/06/2013	44	7

Re: Transpose

Anonymous — Thu, 12 Dec 2013 18:14:10 GMT

Koushik

What does your source data table /model look like ?

Your 'before' does not look like a QlikView table. Is it a spreadsheet or something ?

Best Regards, Bill

Re: Transpose

rbecher — Thu, 12 Dec 2013 22:19:18 GMT

Hi Koushik,

QlikView script is very powerful. You can solve this with some "hand-coding":

// Replace manually if new file has different amount of columns:
FirstRecord:
First 1 LOAD A, 
     B, 
     C, 
     D, 
     E, 
     F, 
     G, 
     H
FROM
D:\Transpose.xlsx
(ooxml, no labels, table is Tabelle1);

// Read all Dates from Header line:
FOR i = 1 to NoOfFields('FirstRecord')
     Let vField = FieldName($(i), 'FirstRecord');
     Dates:
     Load Date#(Date) as DateValue Where Len(Trim(Date))>0;
     Load Text(Peek('$(vField)', -1, 'FirstRecord')) as Date Autogenerate 1;
NEXT

Drop Table FirstRecord;

// Loop over Dates and read additional columns:
FOR i = 0 to NoOfRows('Dates') -1
     Let vDate = Text(Peek('DateValue', $(i), 'Dates'));
     Set vFieldNum = '';
     Set vFierldDenom = '';
     IF $(i) > 0 THEN
         Let vFieldNum = 'Num' & i;
         Let vFierldDenom = 'Denom' & i;
     ELSE
         Set vFieldNum = 'Num';
         Set vFierldDenom = 'Denom';
     END IF
     Result:
     LOAD Code, 
          Name, 
          Date#('$(vDate)') as Month,
          $(vFieldNum) as Num, 
          $(vFierldDenom) as Denom
     FROM
     D:\Transpose.xlsx
     (ooxml, embedded labels, header is 1 lines, table is Tabelle1);
NEXT

Drop Table Dates;

In my example I assumed you have an Excel file. But this could be adapted to CSV or other formats.

- Ralf

Re: Transpose

Fri, 13 Dec 2013 11:13:58 GMT

Hi Bill

The 'Before' table is a spreadsheet.

Re: Transpose

Fri, 13 Dec 2013 11:57:55 GMT

Hi Ralf

The script is unable to read 'Num' and 'Denom'. I get an error

Field not found - <Num>

Result:

LOAD Code,

Name,

Date#('2J') as Month,

Num as Num,

Denom as Denom

FROM

(ooxml, embedded labels, table is Sheet1)

Re: Transpose

rbecher — Fri, 13 Dec 2013 12:05:38 GMT

The format of your Excel file must be different then. You have to adapt it, or upload it that I can have a look.

Re: Transpose

Fri, 13 Dec 2013 12:31:57 GMT

Thanks Ralf. It worked.

Re: Transpose

Thu, 23 Jan 2014 14:07:13 GMT

Hi Ralf

The above code works if I have one excel workbook.

I have multiple workbooks with multiple sheets. How can I loop them? I could get it to work by ODBC the excel workbook but this only works if the workbook has same number of sheets in each workbook.

Re: Transpose

Thu, 23 Jan 2014 14:12:10 GMT

you can use

FOR EACH vSheet IN SheetName_1, SheetName_2...SheetName_n

here is the code given by Ralph

just modify

D:\Transpose.xlsx (ooxml, embedded labels, header is 1 lines, table is Tabelle1);

D:\Transpose.xlsx (ooxml, embedded labels, header is 1 lines, table is $(vSheet));

best regards

Chris

Re: Transpose

Thu, 23 Jan 2014 14:13:32 GMT

And for multiples xls files

SET vFile = Transpose*.xlsx ;

LOAD

...

FROM D:\$(vFile) (ooxml, embedded labels, header is 1 lines, table is $(vSheet));

Re: Re: Transpose

Thu, 23 Jan 2014 14:20:51 GMT

Thanks Chris.

It works if I am using only one work book. Doesnt work if I have multiple workbooks with multiple sheets.Please see attached.

Re: Re: Re: Transpose

Thu, 23 Jan 2014 14:40:00 GMT

Here is a solution using "CrossTable" function

it might be a way to load many files with many sheets

and the script is a lot momre simple

best regards

Chris

Re: Transpose

Thu, 23 Jan 2014 14:51:44 GMT

Thanks Christian.

How can I embed the multiple loading of sheets in Transpose code as that is what I am trying to achieve please.

Re: Transpose

Thu, 23 Jan 2014 14:54:36 GMT

Hi don't understand

in my example i load 2 files with 3 sheets each.

Isn't it what you want?

doesn't matter how many files and sheets you are using

if more than 3 sheets just add in the For each list

chris