Manipulate a table

[Anonymous]

Hi,

I would like to make a table like this:

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

into one like this:

Month	Key	Type Analysis	Type A	Type B
10	001	Object	1	2
10	001	Player	3	12
10	002	Object	12	43
10	002	Player	10	0
11	003	Object	14	42

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:

Month	Key	Type Analysis	Type	Analisys Field	TypeField
10	001	Object Type A	1	Object	Type A
10	001	Object Type B	2	Object	Type B
10	001	Player Type A	3	Player	Type A
10	001	Player Type B	12	Player	Type B

Can you help me? Thanks

[YoussefBelloum]

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 · Accepted solution]

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: