Data Load

[#1]

Hi All,

How to load this salary,comm details.

Thanks in advance.

[Gysbert_Wassenaar]

Perhaps this document helps: multi_header_pivot_import.qvw

[Clever_Anjos]

Maybe you can try this:

t:

CrossTable([42370], Value)

LOAD F1,

     [42370],

     [42402],

     [42434],

     [42466],

     [42498],

     [42530],

     [42562],

     [42594]

FROM

[https://community.qlik.com/servlet/JiveServlet/download/1099824-239817/Book1.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Base:

Load

  F1,

  'Salary' as Type,

  Date(monthstart(trim([42370]))) as Month,

  Value

Resident t ;

DROP Table t;

t:

CrossTable([423701], Value)

LOAD F1,

     [423701],

     [424021],

     [424341],

     [424661],

     [424981],

     [425301],

     [425621],

     [425941]

FROM

[https://community.qlik.com/servlet/JiveServlet/download/1099824-239817/Book1.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Base:

Load

  F1,

  'Comm' as Type,

  Date(monthstart(left(trim([423701]),5))) as Month,

  Value

Resident t ;

DROP Table t;

[effinty2112]

Hi Jack,

                    You can use the file wizard to produce this script that should give you what you're after. It can take a bit of practice to get the best out of this wizard.

The wizard was used to perform a transposition, then field values copied into blank "cells" using the Fill function in the field I called PayType (Salary or Commission), then finished with a crosstable.

Data:

CrossTable(Employee, Payment, 2)

LOAD F1 as PayType,

     Date(F2) as Date,

     Emp,

     Emp1,

     Emp2,

     Emp3,

     Emp4,

     Emp5,

     Emp6,

     Emp7,

     Emp8,

     Emp9,

     Emp10

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

Transpose(),

Replace(1, top, StrCnd(null))

));

Good luck

Andrew

[jagan · Accepted solution]

Hi,

Try like this using Transpose

Data_Temp:

CrossTable(Employee, Value, 2)

LOAD *

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

Transpose()

));

Data:

LOAD

If(Len(Trim(F1)) > 0, F1, Peek('DataType')) AS DataType,

Date(F2, 'MMM-YYYY') AS Month,

Employee,

Value

RESIDENT Data_Temp;

DROP TABLE Data_Temp;

Regards

Jagan.