Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load a Table where some of the columns are values

SurenameLast Name01/01/1501/02/1501/03/15
MarkJacobs111222333
JonyMalon444555666
TonyFernandez777888999

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:

NameDateExpense_TypeExpense_Amount
Mark Jacobs01/01/15Salary111
Mark Jacobs01/02/15Salary222
Jony Malon01/01/15Salary444

And so on...

Any ideas?

1 Solution

Accepted Solutions
sunny_talwar

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;

Capture.PNG

View solution in original post

4 Replies
sunny_talwar

Look at the CrossTable Load: The Crosstable Load

sunny_talwar

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;

Capture.PNG

IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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