topic Data Load in QlikView

Data Load

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

How to load this salary,comm details.

Thanks in advance.

Re: Data Load

Gysbert_Wassenaar — Fri, 05 Aug 2016 12:12:56 GMT

Perhaps this document helps: multi_header_pivot_import.qvw

Re: Data Load

Clever_Anjos — Fri, 05 Aug 2016 12:25:33 GMT

Maybe you can try this:

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;

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;

Re: Data Load

effinty2112 — Fri, 05 Aug 2016 12:32:46 GMT

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

Re: Data Load

jagan — Fri, 05 Aug 2016 14:03:14 GMT

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.