Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

varmekontrol
New Contributor II

using nested-if´s without excluding values

HI

I not sure how to research my way to an answer to this question, as I do not know where to start

I am using Nested if´s (I think that it is what it is called) to make certain categories to be used for analyzing data, and showing the categories totals in charts.

Problem with this solution is that if i choose the first if to look for something all my data includes, then the rest of the if´s will not find anything else.

So how to use something like nested if´s without excluding data when the first if excludes all data?

Example.

cost_receivedpropertyready_for_billing

30-04-18

73486645N
73565511Y
31-05-1873798564Y
73744564N

Category:

Load

Date,

property,

ready_for_billing

If(Date>0 AND ready_for_billing='Y' OR ready_for_billing='N'

,' Ready to be proccesed'

,

if(ready_for_billing='Y'

,' Ready for billing' )) as status

If i use this data load, the 'Ready to be processed' will filter out the 2 lines with date and Y or N

So when the second if is done, the one with the date in cost_recieved, will not be included..

'Ready to be processed' = 2 lines

'Ready for billing' = 1 line. (But I want it to disregard the first if, and get 2 lines)

So how do a make the same categories above, were every if goes through the total, and not only the one that the first if removed?

1 Solution

Accepted Solutions
MVP
MVP

Re: using nested-if´s without excluding values

Maybe this is not possible with if-statement, but what should I use instead?

I think you are right, this might not work with If statement. Instead look to concatenate different buckets and use where condition to restrict each bucket... for example

FinalTable:

LOAD *,

     'lesser than 8' as example

Resident ....

Where data < 8;

Concatenate (FinalTable)

LOAD *,

     'Greater than 5' as example

Resident ....

Where data > 5;

Doing this will help you create overlapping buckets just like you want

8 Replies
undergrinder
Valued Contributor II

Re: using nested-if´s without excluding values

Hi Daniel,

I don't understand exactly what is the need, but I think the problem could be at the first conditional.

Date>0 AND ready_for_billing='Y' OR ready_for_billing='N'

Try use parenthesis in conditions for example:

Date>0 AND (ready_for_billing='Y' OR ready_for_billing='N')


G.

MVP
MVP

Re: using nested-if´s without excluding values

Would you be able to share the expected output for the above sample data?

varmekontrol
New Contributor II

Re: using nested-if´s without excluding values

HI

As you can see in this bar chart and filter pane, then I have 7 if-statements AS anlg_status

Right now if you add all the numbers in the different bars, they will add up to the total.

nestedif.JPG

Problem with this is that that should not be the case, as some of the if-statements applies to multiple properties.

Example.

Data.

1

2

3

4

5

6

7

if(data<8,

.'lesser than 8'

,

if(data>5

,'Greater than 5'

)) as example.

What I am looking for, is a way to get a count of 7 from the first if-statement, and also get the 2 from the second if-statement. I would like to make upwards of 10-15 different if-statements to be used to analyze data with a single field(filter pane)

Maybe this is not possible with if-statement, but what should I use instead?

MVP
MVP

Re: using nested-if´s without excluding values

Maybe this is not possible with if-statement, but what should I use instead?

I think you are right, this might not work with If statement. Instead look to concatenate different buckets and use where condition to restrict each bucket... for example

FinalTable:

LOAD *,

     'lesser than 8' as example

Resident ....

Where data < 8;

Concatenate (FinalTable)

LOAD *,

     'Greater than 5' as example

Resident ....

Where data > 5;

Doing this will help you create overlapping buckets just like you want

varmekontrol
New Contributor II

Re: using nested-if´s without excluding values

Just what i was looking for

Thx.

varmekontrol
New Contributor II

Re: using nested-if´s without excluding values

Hi

This works as intended, but do you know why I now have more data than before?

Especially regarding the tables used in the where clause?

filter_values:

load *,

'udgifter modtaget' as filter

Resident Produktstyring

where udgifter_modtaget>0;


Concatenate (filter_values)

load *,

'udgifter ikke modtaget' as filter

Resident Produktstyring

where udgifter_modtaget<=0;


Concatenate (filter_values)

load *,

'Klar til FSK' as filter

Resident Produktstyring

where aflæsninger_modtaget = 'Y' and aflæsninger_afsluttet = 'Y' and bp_team_landekode='DK';

data-model.JPG

MVP
MVP

Re: using nested-if´s without excluding values

Yes, because if you look at this example

data

1

2

3

4

5

6

7

8

9

10

with this script

FinalTable: -> This load 7 rows

LOAD *,

     'lesser than 8' as example

Resident ....

Where data < 8;

Concatenate (FinalTable) - > This adds another 5

LOAD *,

     'Greater than 5' as example

Resident ....

Where data > 5;

Although, there were 10 rows in the beginning, we ended up with 11 rows because 6 and 7 over lap between the two conditions. One row of 6 and 7 will be flagged with lesser than 8 and another one with Greater than 5... does it make sense?

varmekontrol
New Contributor II

Re: using nested-if´s without excluding values

Yes that makes sense

Thx once again for the great help.