Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

peterviberg
New Contributor II

Combining two columns in one filter?

Hi,

I have a little problem with filtering that I hope you can help me with.

OriginalSplit
OriginalSplit
OriginalSplit
Original
Split
OriginalSplit

All rows are tagged like above. It means that if both "Original" and "Split" columns have a value, that row should always be selected regardless of this specific filter.

If only Original column has a value, then I would like rows with values in both "Original" and "Split" + rows with values in Original column to be shown when "ORIGINAL" is selected in a filter.

If only Split column has a value, I would like rows with value in both "Original" and "Split" + rows with values in Split column to be shown when "SPLIT" is selected in a filter.

So the filter should look like:

Original or split?
Original
Split

This of course could be done my concatenating the two columns, but then I would have to select two of the three concatenated values "OriginalSplit", "Original" and "Split" - and this will be confusing to the user. So I want it to be just two options.

Any ideas how to do this?

Thanks a lot!

//P

Tags (2)
8 Replies
Highlighted
h_prakash
Contributor

Re: Combining two columns in one filter?

Assuming the table is like u mentioned above.

Table 1:

Load Original,

         Split,

        'Original' as Key

Resident Table

where Original = 'Original'

Concatenate (Table 1)

Load Original,

         Split,

        'Split' as Key

Resident Table

where Original = 'Split'

[Original or Split?]:

Load Key

Resident Table 1

peterviberg
New Contributor II

Re: Combining two columns in one filter?

Thanks a lot! It worked with a little change:

Assuming the table is like u mentioned above.

Table 1:

Load Original,

         Split,

        'Original' as Key

Resident Table

where Original = 'Original'

Concatenate (Table 1)

Load Original,

         Split,

        'Split' as Key

Resident Table

where Split= 'Split'

[Original or Split?]:

Load Key

Resident Table 1




Just a question. Is this the optimal way of doing this? Concatenating tables like this is creating a table twice as big as the original.

h_prakash
Contributor

Re: Combining two columns in one filter?

Sure NP. Please mark as correct answer if it is helpfull..!!

peterviberg
New Contributor II

Re: Combining two columns in one filter?

Thanks a lot, and it's helpful. But since I made corrections to your code for it to work it would be confusing to call it the correct answer (since it's actually not correct). Is there any possibility that you can correct your answer? Then I will mark it as correct of course!

h_prakash
Contributor

Re: Combining two columns in one filter?

Not sure of any other alternatives..

Re: Combining two columns in one filter?

Hi Peter, another option can be creating a concatenated Key:

LOAD Split &'|'& Original as SO_Key

          ...

From/Resident...

//Create the related table

[Original or Split]:

LOAD FieldValue(SO_Key, RecNo()) as SO_Key,

          Subfield(FieldValue(SO_Key, RecNo()), '|', 1) as Split

          Subfield(FieldValue(SO_Key, RecNo()), '|', 2) as Original

autogenerate fieldValueCount('SO_Key');

ogster1974
Honored Contributor II

Re: Combining two columns in one filter?

You could try setting a variable under a button Optional or Split. then in your sheet objects you can alter how the object behaves accordingly using the variable rather than a filter. 

Makes Sense to go round in circles

Ive attached an example of it working in action.  Although I think the two suggestions you have already been provided with are easier to implement. Depends if you want to limit the behaviour to just part of an app or the whole of it.  If its all the app then filtering will be the way to go.

Regards

Andy

Quy_Nguyen
Contributor III

Re: Combining two columns in one filter?

Hari,

Just click Actions on the left bottom of your answer then select Edit