topic Re: How to partially cross table with multiple headers in QlikView

How to partially cross table with multiple headers

Sun, 09 Oct 2016 14:52:03 GMT

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!

Re: How to partially cross table with multiple headers

el_aprendiz111 — Mon, 10 Oct 2016 17:31:06 GMT

Hi,

Re: How to partially cross table with multiple headers

Tue, 11 Oct 2016 17:13:19 GMT

HI fer, thank you!

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

Tks!!!

Re: How to partially cross table with multiple headers

el_aprendiz111 — Tue, 11 Oct 2016 19:04:39 GMT

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;

Re: How to partially cross table with multiple headers

MarcoWedel — Tue, 11 Oct 2016 21:53:20 GMT

Hi,

maybe helpful:

multi_header_pivot_import.qvw

regards

Marco

Re: How to partially cross table with multiple headers

Mon, 17 Oct 2016 03:49:10 GMT

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.