How to partially cross table with multiple headers

[#1]

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!

[el_aprendiz111]

Hi,

[#3]

HI fer, thank you!

Could you share the file (qvw or txt of the script) instead of the image?

Tks!!!

[el_aprendiz111]

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;

[MarcoWedel]

Hi,

maybe helpful:

multi_header_pivot_import.qvw

regards

Marco

[#6]

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.