Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
That's brilliant, thank you very much Cristian, I was so close, your solution worked perfectly.
I'm glad Krisslax 🙂
Regarts.