Filter on a specific column depending on an switch indicator

[#1]

Hello,

Let's say I have 3 columns as below

ColumnA, ColumnB, ColumnC

1 Y N
2 Y N
3 Y Y
4 N N

and an Inline Indicator with values (Yes/No).

I would like to filter on Column B=Y if the selected Indicator is "Yes", so records 1, 2 and 3 are returned.

I would like to filter on ColumnC=Y if the selected Indicator is "No", so record 3 is the only record returned.

Can anyone please share their ideas?

Thank you.

[Oleg_Troyansky]

Anthomy,

you can try creating a list box with an Expression - something like "IF(..., FieldB, FieldC)" and see if it solves your problem.

[#3]

Hi Oleg,

Thank you for your response. I would like to have the Indicator to act as a filter either to Column B or Column C depending on value selected.

I created a listbox with the below expression but didn't get the desired result.

=if([Indicator]='Yes', ColumnB, ColumnC)

When I select Yes, both ColumnB and ColumnC were selected with a value of Y.

When I select No, both ColumnB and ColumnC were selected with a value of N.

I was thinking of including this filter in the Load script. Any ideas ?

[Oleg_Troyansky]

If your decision is made in the script, then you can simply link one or the other field - it doesn't even have to be dynamic. Something like this:

load

...,

if([Indicator]='Yes', ColumnB, ColumnC) as SelectedColumn

,

and only provide the list box for "SelectedColumn"

am I missing your need?

[#5]

Hi,

Here's my sample script,

SelectIndicator:
LOAD * INLINE [
"Indicator"
Yes
No
];

Table:
LOAD * INLINE [
"RecNum","ColumnB","ColumnC"
1, Y, N
2, Y, N
3, Y, Y
4, N, Y
5, N, Y
];

As you can see, there is no join between the 2 tables. I would like to filter on either ColumnB or ColumnC depending on the value selected from the Indicator (Yes/No).

If Indicator='Yes', then apply filter on ColumnB='Y', result set is as below,

RecNum, ColumnB, ColumnC
1 Y N
2 Y N
3 Y Y

If Indicator='No', then apply filter on ColumnC='Y', result set is as below,

RecNum, ColumnB, ColumnC
3 Y Y
4 N Y
5 N Y

I hope I explained the requirement much clearer now.

[johnw]

I'd do it like this:

FilterBySwitch:
LOAD
RecNum
,'Yes' as Indicator
RESIDENT Table
WHERE ColumnB = 'Y'
;
CONCATENATE LOAD
RecNum
,'No' as Indicator
RESIDENT Table
WHERE ColumnC = 'Y'
;

[#7]

Hi John,

Thank you for your response. Your code works !

However, I'm loading millions of records here, so when ColumnB and Columnc C are both 'Y', they will be loaded twice, one for Indicator= 'Yes' and one for Indicator='No'. I would like to avoid such scenario.

Is there a way to use a variable and assign that variable the Indicator's Value to filter on the Table ?

Something like this -

LET IndicatorVar = Indicator column from Indicator Table;

Select

RecNum,

ColumnA,

ColumnB

From table

where ((ColumnA = 'Y' and '$(IndicatorVar)'='Yes' ) or (ColumnB = 'Y' and '$(IndicatorVar)'='No' ))

[johnw]

Why do you want to avoid loading the data twice? Time taken during the load? Memory taken during the load? Memory taken in the final product? QlikView, generally speaking, doesn't have the same sort of problems with "redundant" data that most systems have. It's read only, so you can't make a mistake and update one copy but not another. And its compression algorithm should squish the redundant information down pretty small.

In MY experience, the most critical piece of the performance/memory puzzle is the chart performance, because that directly impacts your users. Chart performance is maximized when QlikView's normal associative logic does all the work for you, as it does in my example.

That said, yes, you can do it with a variable and an expression instead of with data. You'll save a little memory and load time, and your chart will be a little slower. Unlike with the data approach, setting the variable will ONLY affect charts with the specialized code, not all charts. Or perhaps I'm misunderstood you. Anyway, see attached.