Cross Table

[bhavvibudagam]

Hi Experts,

Can anyone please help me on this.

I have the output excel file generated from Qlikscript attached below.

From the attached excel file i need to get the output format like below screen shot.

All the Revenue Target, Cost Internal Target,Cost External Target, Cost total Target, Netpofit Target fields data need to get below Target field.

All the Revenue Actual, Cost Internal Actual,Cost External Actual, Cost total Actual, Netpofit Actual fields data need to get below Actual field.

All the Revenue Status, Cost Internal Status,Cost External Status, Cost total Status, Netpofit Status fields data need to get below status field.

KPI name field need to create manually.

Thanks in advance.

[sunny_talwar · Accepted solution]

Try this

Table:

CrossTable([KPI name], Data, 3)

LOAD [Staff ID],

    [Partner name],

    [Investment name],

    [Revenue Target],

    [Revenue Actual],

    [Investment Revenue Status],

    [Target Cost Internal],

    [Actual Cost Internal],

    [Cost Internal Status],

    [Target Cost External],

    [Actual Cost External],

    [Cost External Status],

    [Target Cost Total],

    [Actual Cost Total],

    [Cost Total Status],

    [Target Net Profit],

    [Actual Net Profit],

    [Net Profit Status]

FROM

[..\..\Downloads\Test.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

Table2:

LOAD *,

Pick(WildMatch([KPI name], '*Revenue*', '*Cost Internal*', '*Cost External*', '*Cost Total*', '*Net Profit*'), 'Revenue', 'Cost Internal', 'Cost External', 'Cost Total', 'Net Profit') as KPI_Name,

Pick(WildMatch([KPI name], '*Target*', '*Actual*', '*Status*'), 'Target', 'Actual', 'Status') as Columns

Resident Table;

DROP Table Table;

FinalTable:

LOAD Distinct [Staff ID],

    [Partner name],

    [Investment name],

    KPI_Name

Resident Table2;

FOR i = 1 to FieldValueCount('Columns')

LET vCol = FieldValue('Columns', $(i));

Left Join (FinalTable)

LOAD Distinct [Staff ID],

      [Partner name],

      [Investment name],

      KPI_Name,

      Data as [$(vCol)]

    Resident Table2

    Where Columns = '$(vCol)';

NEXT i

DROP Table Table2;