Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shahfaisalq
Contributor III
Contributor III

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

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

shahfaisalq_0-1589795709937.pngshahfaisalq_1-1589795790236.png

shahfaisalq_2-1589795870020.png

 

Labels (3)
2 Solutions

Accepted Solutions
Gui_Approbato
Creator III
Creator III

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

View solution in original post

shahfaisalq
Contributor III
Contributor III
Author

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

View solution in original post

3 Replies
Gui_Approbato
Creator III
Creator III

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
Contributor III
Contributor III
Author

It's working!

It helps

Thank you so much.

Regards,

Shah

shahfaisalq
Contributor III
Contributor III
Author

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