topic Re: Merge multiple columns to one in QlikView

Merge multiple columns to one

Murlimes — Mon, 25 Jan 2021 16:19:35 GMT

Hi,

I have a matrix in excel which looks like this

User	skill1	skill2	skill3	skill4	skill5
Tom	yes	yes	no	no	yes
Jerry	no	yes	yes	yes	no
Daisy	yes	yes	yes	no	no

Is there a way to load this in to qlikview so i can have a listbox, which let me select the skills?

So the listbox would include skill1,skill2,skill3,skill4,skill5. And by selecting any skill it would just show the users who actually have the skill?

If i select skill2,skill3 , it would show jerry, Daisy.

If i select skill4 it will showm jerry

If i sellect skill4,skill5 there is no match.

Thank you

Murlimes

Re: Merge multiple columns to one

PradeepK — Mon, 25 Jan 2021 16:35:23 GMT

Hi,

Your Data is in CrossTable Format. Try to convert into generic form using Crosstable prefix.

Look into docs - Crosstable Doc

Result should be something like :

User | skill | value

Tom | skill1 | yes

Tom | skill2 | yes

Tom | skill3 | no

.... and so on

In your case you can use

Crosstable (skill, value, 1)

Load

..... Source

Re: Merge multiple columns to one

Murlimes — Tue, 26 Jan 2021 14:39:26 GMT

thx, i already thought about this. I will try it on a small sample and see if its provide the format i need.

Do you maybe have an idea how this can be solved beforehand? I can change the Format in the Excel Sheet, but i dont really know if this would actually help.

Because if i would just do one column called Skills and then fill it with skil1-5 (column would have multiple entries) , i dont see how this would let me create a listbox, it would just list all possible permuttation of all skills in the colums but not each seperate. Is there maybe a qlikview function or custom listbox which can solve this?

Thank you

Murlimes