Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

 

2 Solutions

Accepted Solutions
Highlighted
Creator
Creator

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

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

Highlighted
Contributor III
Contributor III

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

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
Highlighted
Creator
Creator

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

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

Highlighted
Contributor III
Contributor III

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

It's working!

It helps

Thank you so much.

Regards,

Shah

Highlighted
Contributor III
Contributor III

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

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