Load a Table where some of the columns are values

[#1]

Surename	Last Name	01/01/15	01/02/15	01/03/15
Mark	Jacobs	111	222	333
Jony	Malon	444	555	666
Tony	Fernandez	777	888	999

This is my table(Excel 2003 format)...

I need to load it so the first and last name combined are the Key,

but then i want a field called "Expense Type" where all the values are "Salary"

And i want a Field called "Date" where the values are the date header.

After the reload i want to have something like this:

Name	Date	Expense_Type	Expense_Amount
Mark Jacobs	01/01/15	Salary	111
Mark Jacobs	01/02/15	Salary	222
Jony Malon	01/01/15	Salary	444

And so on...

Any ideas?

[sunny_talwar]

Look at the CrossTable Load: The Crosstable Load

[sunny_talwar · Accepted solution]

Code for the sample provided:

Table:

CrossTable(Date, Expense_Amount, 2)

LOAD Surename & ' ' & [Last Name] as Name,

  'Salary' as [Expense_Type],

    [01/01/15],

    [01/02/15],

    [01/03/15]

FROM

[https://community.qlik.com/thread/190995]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

NoConcatenate

LOAD Name,

  Date(Date#(Date, 'MM/DD/YYYY')) as Date,

  Expense_Type,

  Expense_Amount

Resident Table;

DROP Table Table;

[IAMDV]

Hi Shahar,

CrossTable operator does exactly what you need. Here's a simple video tutorial on how to use CrossTable along with Pros and Cons.

http://qlikshare.com/qlikview-video-tutorial-crosstable-qlikview-transpose-data/

Cheers,

DV

[#5]

Thanks alot for your answer,

I feel like sharing, we have an application that reads from several excels, some span over more then 6 months (one file per day)...

My predecesor chose to use python scripts in order to take all these excel files, iterate them, axtract what she needs and save them to .csv files which she later uses the qlikview script editor to load...

The past month or two i spent learning what i think to be the quite complex data model she has created and have come to a conclution that its probably not the most efficient one (using a python script to convert one file to another file loading it with a third file (qvw), exporting that to a qvd and then loading it in a second qvw).

I want to believe she hed her reasons, but thanks to your help i think iv'e discovered those reasons might not be very concreate.

Thanks again for your eye opening answers, if my story interests anybody I would love to speak privatly about it and see if i can get to a conclussion