Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I need help loading a table bringing Month Fields that are currently displayed as columns to be displayed one in each row.
I've atteched a simple excel of example where the source table is in the Source sheet and the result I want in the Expected Result sheet.
My table has three values fror each month (PLAN, PLANC, REAL) and I want to keep them as 3 colums and only cross to rows the Month header.
Could someone please help me with this?
Thaks a lot!
Hi,
HI fer, thank you!
Could you share the file (qvw or txt of the script) instead of the image?
Tks!!!
Hi Igor
tbl_Map: Mapping LOAD * Inline
[
cod, tp
0, PLAN
1,PLANC.
2, REAL
];
Directory;
[t1]:
LOAD F1 AS Pacote,
F2 AS Divisao,
F3 AS Pais,
F4 AS cc,
Jan,Jan1,Jan2,Fev,Fev1,Fev2,Mar,Mar1,Mar2,Abr,Abr1,Abr2,
Mai,Mai1,Mai2
FROM
[..\..\..\..\..\..\Desktop\web_Qlik\BSE_WEB.xlsx]
(ooxml, embedded labels, table is EXMPL, filters(Remove(Row, Pos(Top, 1))))
Where F4<>'CC';
ini: CrossTable(Month, Data,4)
[ini]:LOAD * Resident t1; DROP Table t1;
[RES]:LOAD Pacote,Divisao,Pais,cc, if(KeepChar(Month, '0123456789')='',0,KeepChar(Month,'0123456789'))AS ID,
PurgeChar(Month,'0123456789') as MONTH, Data Resident ini;
[TMP]: LOAD Pacote,Divisao,Pais,cc,RECNO() AS ID, ApplyMap('tbl_Map',ID, Null()) AS TP,MONTH,Data Resident RES;
VIEW:
LOAD Pacote,Divisao,Pais,cc,ID,MONTH,NUM(Data) AS PLAN, 0 AS PLANC, 0 AS REAL Resident TMP Where TP='PLAN';
Right Join
LOAD Pacote,Divisao,Pais,cc,ID,MONTH,NUM(Data) AS PLANC, 0 AS PLAN, 0 AS REAL Resident TMP Where TP='PLANC';
Right Join
LOAD Pacote,Divisao,Pais,cc,ID,MONTH,Data AS REAL, 0 AS PLANC, 0 AS PLAN Resident TMP Where TP='REAL';
DROP Field ID;
DROP Table RES,ini,TMP;
Hi, the values for PLAN and PLANC are always coming back as 0.
I know in the example I uploaded the PLAN and PLANC values were always zero but it was just a coincidence. If a I load your script with any values in these columns they are always ending up zero after all the crosses.
If you add random numbers to theses columns you will see that.