combine two dimension into one dimension with crosstable in load script.

[shahfaisalq]

Hi,

Please help with the following.

I am loading monthly data with a year using cross table function. the data is loading fine. But need a new month and year combine column as a measurement.

how will we combine the month and year field into one column, as shown in excel snapshot. whereas in Qlik we loaded them separately, which is not right.

Looking forward to positive feedback.

Thank you,

Regards,

Shah

[Gui_Approbato · Accepted solution]

You can do a "resident load" with your table.

Your table name is Consumed, so after your code (leave it as it is in your picture) you write:

"Consumed 2":

Load ID,

"ID B",

"Year",

"Type",

"Month Year",

"Month Year"&'-'&"Year"  as "Desired column",

"Quantity Fuel" Resident Consumed;

Drop table Consumed;

So you create a new table named "Consumed 2", call all the columns and create a new one concatenating the month an year, and you do that with the resident load. After that you can drop your previous table and that leaves you with only the "Consumed 2".

Cheers

[shahfaisalq]

It's working!

It helps

Thank you so much.

Regards,

Shah

[shahfaisalq · Accepted solution]

Hi,

Is there a way to use the down below with dual.

"Month Year"&'-'&"Year"  as "Month Year Generation",

I tried this script but it didn't work.

dual(Month("Month Year"),year("Year ")) as "Month Year Generation",

We are doing this because to sort the data in Bar chart in either ascending or descending.

"Consumed 2":

Load ID,

"ID B",

"Year",

"Type",

"Month Year",

// dual(Month("Month Year"),year("Year ")) as "Month Year Generation",

"Month Year"&'-'&"Year"  as "Month Year Generation",

"Quantity Fuel" Resident Consumed;

Drop table Consumed;

looking forward to your feedback.

Thank you,

Regards,

Shah