Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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_received | property | ready_for_billing |
---|---|---|
30-04-18 | 73486645 | N |
73565511 | Y | |
31-05-18 | 73798564 | Y |
73744564 | N |
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?
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
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.
Would you be able to share the expected output for the above sample data?
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.
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?
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
Just what i was looking for
Thx.
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';
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?
Yes that makes sense
Thx once again for the great help.