Transpose

[#1]

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

[Anonymous]

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

[rbecher · Accepted solution]

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

[#4]

Hi Bill

The 'Before' table is a spreadsheet.

[#5]

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)

[rbecher]

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

[#7]

Thanks Ralf. It worked.

[#8]

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. 

[#9]

Hi

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

by

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

NEXT

best regards

Chris

[#10]

And for multiples xls files

SET vFile =  Transpose*.xlsx ;

LOAD

...

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