Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Pedro_Rodriguez
New 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!

Tags (1)
1 Solution

Accepted Solutions
effinty2112
Honored Contributor

Re: Concatenate several fields per row in script table

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
Honored Contributor

Re: Concatenate several fields per row in script table

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
New Contributor III

Re: Concatenate several fields per row in script table

Thanks Andrew, very helpful.