Crosstable with year and months

[krisslax]

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

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.

[cristianj23a · Accepted solution]

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.

[krisslax]

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

[cristianj23a]

I'm glad Krisslax 🙂

Regarts.