Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
krisslax
Contributor II
Contributor II

Crosstable with year and months

Hi - I've struggled with this for 2 days and know I'm missing something really easy but I'm so involved with it now I can't see the wood for the trees.

 

So, I have a table that I've loaded into Qlik similar to the following:

ID Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
250 2023 0 0 0 0 0 0 26500 0 0 10000 0 0
250 2024 4000 0 0 0 0 0 0 0 0 0 0 0
251 2023 0 0 0 0 2500 3000 0 0 0 0 0 0

 

krisslax_0-1689837466996.png

It details how much spend we're forecasting against various Projects.

I'd like it to be like this:

ID Date Value
250 Jan-23 0
250 Feb-23 0
250 Mar-23 0
250 Apr-23 0
250 May-23 0
250 Jun-23 0
250 Jul-23 26500
250 Aug-23 0
250 Sep-23 0
250 Oct-23 10000
250 Nov-23 0
250 Dec-23 0

 

Any help would be greatly appreciated.

Labels (2)
1 Solution

Accepted Solutions
cristianj23a
Partner - Creator III
Partner - Creator III

Hello, here I send you the code.
You just have to change the path of the file that is from the "from" command.
I also attached the test cel.

Data:
CrossTable(Month,Value,2)
LOAD
[ID],
[Year],
[Jan],
[Feb],
[Mar],
[Apr],
[May],
[Jun],
[Jul],
[Aug],
[Sep],
[Oct],
[Nov],
[Dec]
FROM [lib://DataFiles/TestCrossTable.xlsx]
(ooxml, embedded labels, table is Hoja1);

Table:
Load
[ID],
[Year],
[Month],
Month&'-'&Right(Year,2) AS [Month-Year],
Value
Resident Data;
Drop Table Data;

Regards.

 

 

 

https://www.linkedin.com/in/cristianjorge/
Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.

View solution in original post

3 Replies
cristianj23a
Partner - Creator III
Partner - Creator III

Hello, here I send you the code.
You just have to change the path of the file that is from the "from" command.
I also attached the test cel.

Data:
CrossTable(Month,Value,2)
LOAD
[ID],
[Year],
[Jan],
[Feb],
[Mar],
[Apr],
[May],
[Jun],
[Jul],
[Aug],
[Sep],
[Oct],
[Nov],
[Dec]
FROM [lib://DataFiles/TestCrossTable.xlsx]
(ooxml, embedded labels, table is Hoja1);

Table:
Load
[ID],
[Year],
[Month],
Month&'-'&Right(Year,2) AS [Month-Year],
Value
Resident Data;
Drop Table Data;

Regards.

 

 

 

https://www.linkedin.com/in/cristianjorge/
Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.
krisslax
Contributor II
Contributor II
Author

That's brilliant, thank you very much Cristian, I was so close, your solution worked perfectly.

cristianj23a
Partner - Creator III
Partner - Creator III

I'm glad Krisslax 🙂

Regarts.

 
https://www.linkedin.com/in/cristianjorge/
Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.