How to create new columns in the load script?

[jasonwills]

Hi All,

not sure how to explain this properly, so hopefully it will be clear.

I have data from an excel sheet, an example below:

In the load scrip i have:

Load

     A as Country,

     B as JanBudget,

     C as FebBudget,

     D as MarBudget,

     E as AprBudget,

     F as MayBudget.........

However, that is not quite what i'd like.

I'd like to have the number 1-5 (in the example above) as the Month, so Jan, Feb etc...

I'd also like column A as the Country (no problem),

But the figures i'd prefer in one field called MonthlyBudget.

So i would be left with the following

Country = Austria

Month = Jan

MonthlyBudget = 45000,

Country = Austria

Month = Feb

MonthlyBudget = 55000.......

hopefully i have explained it, but please comment if i am unclear, and thanks for any help you can give.

Regards

Jason

[devarasu07 · Accepted solution]

Hi,

By Using Cross table method you can able achieve it. Tks

The Crosstable Load

Regards,

Deva

[sunny_talwar]

Are you looking for this may be?

The Crosstable Load

[devarasu07]

Hi,

Please refer to the attachment based on your source. tks

Cross Table syntax:

CrossTable([Field name for the headers], [Field Name for the Data values], [# of Qualifier fields])

Example:

  [CrossTable]:
CrossTable(Month, Sales,1)

LOAD

County,
Jan,
Feb,
Mar,
Apr,
May
FROM

(ooxml, embedded labels, table is Sheet1);

Thanks,

Deva

[MarcoWedel]

please post a sample excel file.

thanks

regards

Marco

[devarasu07]

Hi Jason,

If your issue resolved, help to close this thread. it might helpful for others. Thanks

Going forward post the mock data set instead of screen image.

Note: Now Qlik has introduced new Short Cut key (CTRL + Q + Q)  to create mock data set.

Thanks, Deva

[jasonwills]

Perfect, thanks for the pointers Devarasu - really helps.

I'll have a play with it and see what i can come up with. Also, thanks for the pointers with posting.

Many Thanks

Jason