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