Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
tiziano_gelso_a
New Contributor

Manipulate a table

Hi,

I would like to make a table like this:

MonthKeyObject Type AObject Type BPlayer Type APlayer Type B

10

00112312
100021223100
11003144229
1200416450

into one like this:

MonthKeyType AnalysisType AType B
10001Object12
10001Player312
10002Object1243
10002Player100
11003Object1442

I tried to make this:


Crosstable (TypeAnalysis, Type, 2)

[TEST_TABLE]:

LOAD

      Month,

     Key,

     Object Type A,

     Object Type B,

     Player Type A,

     Player Type B

FROM [lib://QVD/TEST_TABLE.QVD]

(qvd);

[TEST_TABLE_DERIVATE]:

LOAD *,

    if (SubStringCount((UPPER(TypeAnalysis)),'OBJECT')<>0,'Object' , 'Player', as AnalysisField,

      if (SubStringCount((UPPER(Dimensione)),'A')=0,'Type A','Type B') as TypeField

resident TEST_TABLE;


But , in this way, I make this:


MonthKeyType AnalysisTypeAnalisys FieldTypeField
10001Object Type A1ObjectType A
10

001

Object Type B2ObjectType B
10001Player Type A3PlayerType A
10001Player Type B12PlayerType B


Can you help me? Thanks

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor II

Re: Manipulate a table

Try :

t:

load * inline [

Month, Key, Object Type A, Object Type B, Player Type A, Player Type B

10, 001, 1, 2, 3, 12

10 ,002, 12, 23, 10, 0

11 ,003, 14 ,42, 2, 9

12 ,004, 16 ,4, 5 ,0

];

final:

CrossTable(TypeAnalysis, TypeA,2)

load Month, Key, "Object Type A" as Object , "Player Type A" as Player Resident t;

f:

CrossTable(TypeAnalysis, TypeB,2)

load Month, Key, "Object Type B" as Object , "Player Type B" as Player Resident t;

LEFT JOIN(final)

load * Resident f;

drop Table f;

drop Table t;

result:

Capture.PNG

2 Replies
YoussefBelloum
Esteemed Contributor

Re: Manipulate a table

Hi,

I suppose your crosstable is correct, try this:

Crosstable (TypeAnalysis, Type, 2)

[TEST_TABLE]:

LOAD

      Month,

     Key,

     Object Type A,

     Object Type B,

     Player Type A,

     Player Type B

FROM [lib://QVD/TEST_TABLE.QVD]

(qvd);


table2:

load TextBetween(Type,'',' ') as Type1, * Resident TEST_TABLE;

drop table TEST_TABLE;


==> check if the field Type1 is ok for you

OmarBenSalem
Esteemed Contributor II

Re: Manipulate a table

Try :

t:

load * inline [

Month, Key, Object Type A, Object Type B, Player Type A, Player Type B

10, 001, 1, 2, 3, 12

10 ,002, 12, 23, 10, 0

11 ,003, 14 ,42, 2, 9

12 ,004, 16 ,4, 5 ,0

];

final:

CrossTable(TypeAnalysis, TypeA,2)

load Month, Key, "Object Type A" as Object , "Player Type A" as Player Resident t;

f:

CrossTable(TypeAnalysis, TypeB,2)

load Month, Key, "Object Type B" as Object , "Player Type B" as Player Resident t;

LEFT JOIN(final)

load * Resident f;

drop Table f;

drop Table t;

result:

Capture.PNG