Transform

[Anonymous]

Hi,

I am trying to transform the data. With source data as Table , I want the output as displayed in Table 2.

Is it possible?

Table 1

Header 1	Header 2	Header 3
Game1	Jhon	1
Game2	Jhon	3
Game1	Paul	0
Game1	Mark	2
Game1	Luke	1
Game3	Jhon	0
Game2	Paul	1
Game2	Luke	1
Game3	Mark	2
Game2	Jhon	10
Game2	Tom	10

Table 2

game	Jhon	Paul	Mark	Luke	Tom
Game1	1	0	2	1
Game2	13	1		1	10
Game3	0		2

[devarasu07]

Hi Amar,

You want to do in Script? or front end?

if front end then by using pivot table able achieve it. back end (script) u have to go for Cross table method. Thanks

FYI, attached pivot table method

[dineshsingh]

Hi Amar,

If you want to transform this table 1 to table 2 in Script then please go thru The Generic Load article. If you want to do this in UI then please use Pivot table as described by devarasu07.

Regards

Dinesh

[MarcoWedel]

one example using generic load as suggested by Dinesh:

table1:
Generic
LOAD [Header 1] as game,
     [Header 2],
     [Header 3]
FROM [https://community.qlik.com/thread/248723] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 13))));

the pivot table solution seems more straight forward though

hope this helps

regards

Marco

[vinieme12]

Just Aggregating the values during load

temp:

Generic

LOAD [Header 1],

    [Header 2],

    sum([Header 3]) as Values 

FROM [https://community.qlik.com/thread/248723] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 13))))

group by [Header 1],[Header 2];

Set vListOfTables = ;

  For vTableNo = 0 to NoOfTables()-1

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='temp' Then

        Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

      End If

  Next vTableNo

CombinedGenericTable:

  LOAD Distinct [Header 1]

FROM

[https://community.qlik.com/thread/248723]

(html, codepage is 1252, embedded labels, table is @1);

  For each vTableName in $(vListOfTables)

      Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

      Drop Table [$(vTableName)];

  Next vTableName

[vinieme12]

Hi marcowedel‌,

How are you handling the duplicate rows created for Game2 without aggregating?

Please can you post the script; i'm using PE, so can't view your app.

Thanks

Vineeth

[ahaahaaha]

Hi Amar,

another one version code to transform of table at load script.

SET varName = '';

TableTemp:  //load table1 as temp table

Directory;

LOAD Header1,

    Header2,

    Header3

FROM

the path to your start table1;

TableTempName:  //create a new list of field names in temp table

LOAD Distinct

Header2 as NamesRow

Resident TableTemp;

Table: //create the first field of the main table "game"

Load Distinct

Header1 as game

Resident TableTemp;

For varTab = 1 to NoOfRows('TableTempName') //consistently go through the list of names in the temporary table                                                                                  //TableTempName

  varName = FieldValue('NamesRow', $(varTab))  //variable assign next name from the list

  Left Join (Table)                                              //add to the base table a new field with yet another name

  LOAD

  Header1 as game,

  Sum(Header3) as $(varName)

  Resident TableTemp

  Where Header2 = '$(varName)'

  Group By Header1;

NEXT varTab

DROP Tables TableTemp, TableTempName;      //remove the temporary tables

Result

Yet I note, in practice it is this transformation of data often realized after loading the script when creating charts.

Regards,

Andrey

[MarcoWedel]

Hi,

I didn't aggregate during load in this example. Instead I used a straight table with Sum() aggregations on all fields.

Your solution using aggregation during load seems to be the better approach in this case, except there would be additional fields in the source data that distinct the multiple rows per game and name. But then again one could also use a pivot table without the need for any transformation during load at all...

Another version of your approach might be:

tabTemp:
Generic
LOAD [Header 1] as game,
    [Header 2],
    Sum([Header 3])
FROM [https://community.qlik.com/thread/248723] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 13))))
Group By [Header 1], [Header 2];

[CombinedGenericTable]: LOAD 1 AutoGenerate 0;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'tabTemp.*') THEN
  JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

DROP Field 1;

hope this helps

regards

Marco