Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

amar2016
New Contributor II

Transform

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 1Header 2Header 3
Game1Jhon1
Game2Jhon3
Game1Paul0
Game1Mark2
Game1Luke1
Game3Jhon0
Game2Paul1
Game2Luke1
Game3Mark2
Game2Jhon10
Game2Tom10

Table 2

gameJhonPaulMarkLukeTom
Game11021
Game2131110
Game302
7 Replies
devarasu07
Honored Contributor II

Re: Transform

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

1.jpg

Partner
Partner

Re: Transform

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

Highlighted

Re: Transform

one example using generic load as suggested by Dinesh:

QlikCommunity_Thread_248723_Pic2.JPG

QlikCommunity_Thread_248723_Pic3.JPG

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
Esteemed Contributor II

Re: Transform

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

Sum_generic.JPG

vinieme12
Esteemed Contributor II

Re: Transform

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
Honored Contributor

Re: Transform

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


4.jpg

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

Regards,

Andrey

Re: Transform

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