Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
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
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
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
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
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