Concatenate several fields per row in script table

[Pedro_Rodriguez]

Hi everyone,

I'm facing an issue, I have a table like this:

TABLE:   

proveed	type	rank_1	rank_2	rank_3	rank_4	rank_5	rank_6
PROV1	type4						4
PROV1	type1						3
PROV1	type3						2
PROV1	type2						1
PROV2	type4		1
PROV2	type5		4
PROV2	type1		2
PROV2	type6		3
PROV3	type4			4
PROV3	type5			3
PROV3	type7			1
PROV3	type8			2
PROV4	type4	2
PROV4	type6	3
PROV4	type2	1
PROV5	type5					3
PROV5	type7					1
PROV5	type8					2
PROV6	type1				1

And I want to get a table like this:

proveed	type	rank
PROV1	type4	4
PROV1	type1	3
PROV1	type3	2
PROV1	type2	1
PROV2	type4	1
PROV2	type5	4
PROV2	type1	2
PROV2	type6	3
PROV3	type4	4
PROV3	type5	3
PROV3	type7	1
PROV3	type8	2
PROV4	type4	2
PROV4	type6	3
PROV4	type2	1
PROV5	type5	3
PROV5	type7	1
PROV5	type8	2
PROV6	type1	1

That is field rank_* concatenated by row. That's easy to achieve, the problem is that the number of fields rank_* is variable.

The solution is getting something like

LOAD

     proveed,

     type,

     rank_1&rank_2&rank_3&rank_4&rank_5&rank_i... as rank

Resident TABLE;

Is there any solution for this.

Find enclosed this example in a qvw.

Thanks a lot in advance!

[effinty2112 · Accepted solution]

Hi Pedro,

The crosstable function is what you need:

Data:

Crosstable (Rank, Value, 2)

LOAD * INLINE [

    proveed, type, rank_1, rank_2, rank_3, rank_4, rank_5, rank_6

    PROV1, type4, , , , , , 4

    PROV1, type1, , , , , , 3

    PROV1, type3, , , , , , 2

    PROV1, type2, , , , , , 1

    PROV2, type4, , 1, , , ,

    PROV2, type5, , 4, , , ,

    PROV2, type1, , 2, , , ,

    PROV2, type6, , 3, , , ,

    PROV3, type4, , , 4, , ,

    PROV3, type5, , , 3, , ,

    PROV3, type7, , , 1, , ,

    PROV3, type8, , , 2, , ,

    PROV4, type4, 2, , , , ,

    PROV4, type6, 3, , , , ,

    PROV4, type2, 1, , , , ,

    PROV5, type5, , , , , 3,

    PROV5, type7, , , , , 1,

    PROV5, type8, , , , , 2,

    PROV6, type1, , , , 1,

];

Result:

Load

proveed,

type,

Value as rank

Resident Data where Value >0;

Drop table Data;

giving:

proveed	type	rank
PROV1	type1	3
PROV1	type2	1
PROV1	type3	2
PROV1	type4	4
PROV2	type1	2
PROV2	type4	1
PROV2	type5	4
PROV2	type6	3
PROV3	type4	4
PROV3	type5	3
PROV3	type7	1
PROV3	type8	2
PROV4	type2	1
PROV4	type4	2
PROV4	type6	3
PROV5	type5	3
PROV5	type7	1
PROV5	type8	2
PROV6	type1	1

Regards

Andrew

[Pedro_Rodriguez]

Thanks Andrew, very helpful.