topic Re: Create a field with Rangesum and Countif Expressions in QlikView

Create a field with Rangesum and Countif Expressions

mrthomasshelby — Mon, 23 Apr 2018 07:54:55 GMT

Hello,

I have a table shown in the attached excel file.

Now my requirement in that I want to append a new column to this table that counts a particular response('Quoted') in certain predefined columns for each line in the table. For the 'Space Type' 'Onsite', I want to count the number of columns from the below list of 19 columns that have the response 'Quoted' in them:

Minimum Proposed Years of Experience, Breakdown1, Breakdown2,Breakdown3,Breakdown4,Breakdown5,Breakdown6,Breakdown7,Weekday Standard (Turnaround / Shutdown) Hourly Rate,Weekday Overtime Multiplier,Weekday Standard Daily Rate,Weekend Multiplier,Weekend Overtime Multiplier,Weekend Daily Rate,Public Holiday Multiplier,Public Holiday Daily Rate,Monthly Rate,

Mobilisation / Demobilisation Overland Transport Rate Per KM For miles over 170KM,Per Diem For Travel over 170KM

For example, for line A1, all these fields are recorded as 'Quoted' so the column will have the value '19' in it.

Similarly, when 'Space Type' is 'Offsite', the count needs to be taken from the list of 15 columns below:

Mobilisation / Demobilisation Overland Transport Rate Per KM For miles over 170KM,Per Diem For Travel over 170KM

For example for line A351, all the columns in the list above are recorded as 'Quoted' so the column will have the number '15'.

I prefer this expression to be written in my script so that I can use it in further formulae in the front end. I have tried writing the formula as:

if([Space Type]='Onsite', RangeSum(Countif([Minimum Proposed Years of Experience]='Quoted'),Countif(Breakdown1='Quoted'),Countif(Breakdown2='Quoted'),Countif(Breakdown3='Quoted'),Countif(Breakdown4='Quoted'),Countif(Breakdown5='Quoted'),Countif(Breakdown6='Quoted'),Countif(Breakdown7='Quoted'),Countif([Weekday Standard (Turnaround / Shutdown) Hourly Rate]='Quoted'),Countif([Weekday Overtime Multiplier]='Quoted'),Countif([Weekday Standard Daily Rate]='Quoted'),Countif([Weekend Multiplier]='Quoted'),Countif([Weekend Overtime Multiplier]='Quoted'),Countif([Weekend Daily Rate]='Quoted'),Countif([Public Holiday Multiplier]='Quoted'),Countif([Public Holiday Daily Rate]='Quoted'),Countif([Monthly Rate]='Quoted'),Countif([Mobilisation / Demobilisation Overland Transport Rate Per KM For miles over 170KM]='Quoted'),Countif([Per Diem For Travel over 170K]='Quoted')),

if([Space Type]='Offsite',RangeSum(Countif([Minimum Proposed Years of Experience]='Quoted'),Countif(Breakdown1='Quoted'),Countif(Breakdown2='Quoted'),Countif(Breakdown3='Quoted'),Countif(Breakdown4='Quoted'),Countif(Breakdown5='Quoted'),Countif(Breakdown6='Quoted'),Countif(Breakdown7='Quoted'),Countif([Weekday Standard (Turnaround / Shutdown) Hourly Rate]='Quoted'),Countif([Weekday Overtime Multiplier]='Quoted'),Countif([Weekday Standard Daily Rate]='Quoted'),Countif([Standby Hourly Rate]='Quoted'),Countif([Monthly Rate]='Quoted'),Countif([Mobilisation / Demobilisation Overland Transport Rate Per KM For miles over 170KM]='Quoted'),Countif([Per Diem For Travel over 170K]='Quoted')))) as Count1

But it doesnt seem to work. Please help me define the expression for this column. Thanks in advance!

stalwar1‌ kaushik.solanki‌ tresesco‌

Re: Create a field with Rangesum and Countif Expressions

sunny_talwar — Mon, 23 Apr 2018 09:07:17 GMT

Try this

if([Space Type]='Onsite',

SubStringCount([Minimum Proposed Years of Experience]&Breakdown1&Breakdown2&Breakdown3&Breakdown4&Breakdown5&Breakdown6&Breakdown7&[Weekday Standard (Turnaround / Shutdown) Hourly Rate]&

[Weekday Overtime Multiplier]&[Weekday Standard Daily Rate]&[Weekend Multiplier]&[Weekend Overtime Multiplier]&[Weekend Daily Rate]&[Public Holiday Multiplier]&[Public Holiday Daily Rate]&

[Monthly Rate]&[Mobilisation / Demobilisation Overland Transport Rate Per KM For miles over 170KM]&[Per Diem For Travel over 170KM], 'Quoted'),

if([Space Type]='Offsite',

SubStringCount([Minimum Proposed Years of Experience]&Breakdown1&Breakdown2&Breakdown3&Breakdown4&Breakdown5&Breakdown6&Breakdown7&[Weekday Standard (Turnaround / Shutdown) Hourly Rate]&

[Weekday Overtime Multiplier]&[Weekday Standard Daily Rate]&[Standby Hourly Rate]&[Monthly Rate]&[Mobilisation / Demobilisation Overland Transport Rate Per KM For miles over 170KM]&

[Per Diem For Travel over 170KM], 'Quoted'))) as Count1

Re: Create a field with Rangesum and Countif Expressions

mrthomasshelby — Mon, 23 Apr 2018 09:25:05 GMT

Thanks a lot Sunny! I just got to know that the SubStringCount function exists just for counting responses in the columns like this. Perfect! Another day another function learnt! Thanks a lot!

Re: Create a field with Rangesum and Countif Expressions

mrthomasshelby — Mon, 23 Apr 2018 12:48:52 GMT

Hi Sunny. I have just one clarification. The responses to the above mentioned cells can be 3 types: either 'Quoted' or 'Not Quoted' or can be NULL. The issue that I'm facing is with the above expression, even when the response is 'Not Quoted', the Count is happening for the column as 'Quoted' string is a part of 'Not Quoted' too. Is there any qualifier that can be added so that we can consider only 'Quoted' and not 'Not Quoted' while counting the columns?

Re: Create a field with Rangesum and Countif Expressions

sunny_talwar — Mon, 23 Apr 2018 12:58:14 GMT

Try this... changed it for Onsite only.. but wrap everything with | and check for |Quoted|

if([Space Type]='Onsite',

if([Space Type]='Offsite',

SubStringCount([Minimum Proposed Years of Experience]&Breakdown1&Breakdown2&Breakdown3&Breakdown4&Breakdown5&Breakdown6&Breakdown7&[Weekday Standard (Turnaround / Shutdown) Hourly Rate]&

[Weekday Overtime Multiplier]&[Weekday Standard Daily Rate]&[Standby Hourly Rate]&[Monthly Rate]&[Mobilisation / Demobilisation Overland Transport Rate Per KM For miles over 170KM]&

[Per Diem For Travel over 170KM], 'Quoted'))) as Count1