Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Pedro_Rodriguez
Contributor III
Contributor III

Concatenate several fields per row in script table

Hi everyone,

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

TABLE:   

proveedtyperank_1rank_2rank_3rank_4rank_5rank_6
PROV1type4 4
PROV1type1 3
PROV1type3 2
PROV1type2 1
PROV2type4 1
PROV2type5 4
PROV2type1 2
PROV2type6 3
PROV3type4 4
PROV3type5 3
PROV3type7 1
PROV3type8 2
PROV4type42
PROV4type63
PROV4type21
PROV5type5 3
PROV5type7 1
PROV5type8 2
PROV6type1 1

And I want to get a table like this:

   

proveedtyperank
PROV1type44
PROV1type13
PROV1type32
PROV1type21
PROV2type41
PROV2type54
PROV2type12
PROV2type63
PROV3type44
PROV3type53
PROV3type71
PROV3type82
PROV4type42
PROV4type63
PROV4type21
PROV5type53
PROV5type71
PROV5type82
PROV6type11

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!

1 Solution

Accepted Solutions
effinty2112
Master
Master

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
PROV1type13
PROV1type21
PROV1type32
PROV1type44
PROV2type12
PROV2type41
PROV2type54
PROV2type63
PROV3type44
PROV3type53
PROV3type71
PROV3type82
PROV4type21
PROV4type42
PROV4type63
PROV5type53
PROV5type71
PROV5type82
PROV6type11

Regards

Andrew

View solution in original post

2 Replies
effinty2112
Master
Master

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
PROV1type13
PROV1type21
PROV1type32
PROV1type44
PROV2type12
PROV2type41
PROV2type54
PROV2type63
PROV3type44
PROV3type53
PROV3type71
PROV3type82
PROV4type21
PROV4type42
PROV4type63
PROV5type53
PROV5type71
PROV5type82
PROV6type11

Regards

Andrew

View solution in original post

Pedro_Rodriguez
Contributor III
Contributor III

Thanks Andrew, very helpful.