topic Filter on a specific column depending on an switch indicator in QlikView

Filter on a specific column depending on an switch indicator

Thu, 29 Apr 2010 01:26:40 GMT

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.

Filter on a specific column depending on an switch indicator

Oleg_Troyansky — Thu, 29 Apr 2010 03:33:49 GMT

Anthomy,

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

Filter on a specific column depending on an switch indicator

Thu, 29 Apr 2010 07:44:02 GMT

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 ?

Filter on a specific column depending on an switch indicator

Oleg_Troyansky — Thu, 29 Apr 2010 14:35:06 GMT

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?

Filter on a specific column depending on an switch indicator

Fri, 30 Apr 2010 01:56:33 GMT

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.

Filter on a specific column depending on an switch indicator

johnw — Fri, 30 Apr 2010 21:38:30 GMT

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'
;

Filter on a specific column depending on an switch indicator

Thu, 13 May 2010 20:33:40 GMT

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' ))

Filter on a specific column depending on an switch indicator

johnw — Thu, 13 May 2010 21:07:22 GMT

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.