topic Re: Transform in QlikView

Transform

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Transform

devarasu07 — Sat, 04 Feb 2017 16:33:12 GMT

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

Re: Transform

dineshsingh — Sat, 04 Feb 2017 18:53:23 GMT

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

Re: Transform

MarcoWedel — Sat, 04 Feb 2017 21:23:26 GMT

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

Re: Transform

vinieme12 — Sun, 05 Feb 2017 06:01:59 GMT

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

Re: Transform

vinieme12 — Sun, 05 Feb 2017 06:04:49 GMT

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

Re: Transform

ahaahaaha — Sun, 05 Feb 2017 12:59:50 GMT

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

Re: Transform

MarcoWedel — Sun, 05 Feb 2017 17:41:46 GMT

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