I have anumber of Bit flags like IsReturn,IsVoid etc. on a transaction table.
Id like a single column in a straight table that looks to see which one is true and returns the String 'Return',Void etc without building a nightmare nested if statement
Is there a suitable qlikView Function for the Job ?
Frankly, I'd prefer "a nightmare nested if statement". Even with just the sample data above, we're approaching the limit of the two line format. Once you start going to multiple lines, it becomes much more difficult to see which flag is associated with which value.
I'd also prefer "a nightmare nested if statement" to your concatenated series of ifs. With the nested if, it only goes until it finds the first flag that is true. With your concatenated series of ifs, it must check every flag every time, which on average would be twice as slow. And if you move your most common flags to the front, the series of ifs would be even worse by comparison.
Frankly, I'd prefer "a nightmare nested if statement". Even with just the sample data above, we're approaching the limit of the two line format. Once you start going to multiple lines, it becomes much more difficult to see which flag is associated with which value.
I'd also prefer "a nightmare nested if statement" to your concatenated series of ifs. With the nested if, it only goes until it finds the first flag that is true. With your concatenated series of ifs, it must check every flag every time, which on average would be twice as slow. And if you move your most common flags to the front, the series of ifs would be even worse by comparison.
Thanks for the pick() function, I agree with you that its not a very neat solution though.
The Left join to an Inline table also appeals as it can go in the Overnight QVD Load Script. and then has no further processing effect on the QVW but i need to Retain the IslineVoid type fields as the script contains sum(islineVoid) to count the number of Voids (why using 1 to represent true for this).
Mapping load i havnt used as yet but no time like the present, it can also go in the overnight Load script. I presume I can drop the mapping table on completion.
It also appears from my Data that I can have a single line transaction that is both LineVoid and TransactionVoid. i can see that the mapping Load could handle this.
Mapping loads are automatically dropped, I believe at script completion. I can't remember if you get an error if you try to drop one manually, but it isn't necessary.
because I also had some projects where I could not / did not want to avoid the nested ifs and I agree with you that nested if statements are sometimes a nightmare, I have created a little tool which helps me to avoid syntax errors while creating the if-statements.
Sum(if(Enroll_FY09 = 'Yes' and TreatmentOnly <> 'Y' and EnrolledInWHN = 'Y', 1, 0))
,Sum(if(Enroll_FY10 = 'Yes' and TreatmentOnly <> 'Y' and EnrolledInWHN = 'Y', 1, 0))
,Sum(if(Enroll_11 = 'Enroll_2011' and TreatmentOnly <> 'Y' and EnrolledInWHN = 'Y', 1, 0))
)
Where [Capacity Year] = a selector box with values = 2009, 2010, 2011 ... Enroll_09 is a field in my data with the value of '2009' or NULL, Enroll_10 = '2010' or NULL ...
Hi,
I had a similar problem to the user above and created this code in a straight table to try to solve my problem. The column "Total Enrolled" is the old nested if statement that just covers FY2009 and FY2010, the New column uses the formula above. The fields Enroll_09, Enroll_10, Enroll_11 are not mutually exclusive though - a client can be enrolled in multiple years. The chart works fine at a detail level, by client Id or rolled up by the Enroll_09, Enroll_10, Enroll_11 fields (see below) and changes appropriately when new years are chosen in the selector box, b/c the formula above can count distinct instances - but when I try to aggregate at a higher level (by Alias- Site), the code breaks down. I started with a nested if statement that worked fine for the first 2 fiscal years, but I want to be able to add multiple years to the chart without hard coding - this is messy, but i can just add a new line for every new fy i add to the data.
Enroll_09
Enroll_10
Enroll_11
Total Enrolled
New
8573
8573
2009
2010
6005
6005
2009
2010
2011
2387
2387
2009
-
2009
2011
-
2010
136
136
2010
2011
45
45
Can anyone help?? This is the result I get when I roll up by Alias. I'll also take suggestions for a new approach... I want users to be able to select the FY they want via a selector box and be able to dynamically select which variable (Enroll_09, Enroll_10, Enroll_11 ... ) will be used in the formula.
