topic Re: Inline Load with Spaces in QlikView

Inline Load with Spaces

carlcimino — Mon, 10 Apr 2017 15:29:49 GMT

Hello, The inline load works for me. However when I set dimensions and measures in my Object as conditional they don't respond as desired. For example when I select dimension = Year Built and measure = Account Month Issues I get the image below. It looks like it has to do with the dimension/measure ending in the same word? How do you get around?

dimension conditions

=SubStringCount(Concat(_dimension, '|'), 'Year Built')

measure condition

=SubStringCount(Concat(_measure, '|'), 'Account Month Issues')

set Verbatim=1;

Dimensions:
LOAD * INLINE [_dimension
"State"
"County"
"City"
"LOB"
"Segment"
"Month"
"Year"
"Branch"
"Region"
"Producer"
"BDM"
"Producer Tier"
"Prior Carrier"
"ACE/Fund/Chubb"
"Amount of Insurance"
"Year Built"
"Deductible"
"Residence Type"
"Number of Drivers"
"Number of Vehicles"
"Number of VAC Items"
"Liability Limit"
];

Measures:
Load * INLINE [_measure
"Quoted Premium"
"Issued Premium"
"Quotes"
"Issues"
"Premium Hit Ratio"
"Stick Count"
"Account Month Issues"
"Account Month Issued Premium"
"Current Quoted Premium"
"Prior Quoted Premium"
"Current Quotes"
"Prior Quotes"
];

Re: Inline Load with Spaces

sunny_talwar — Mon, 10 Apr 2017 15:34:03 GMT

Try these:

dimension conditions

=SubStringCount(Concat('|' & _dimension & '|', ','), '|Year Built|')

measure condition

=SubStringCount(Concat('|' & _measure & '|', ','), '|Account Month Issues|')

Re: Inline Load with Spaces

carlcimino — Mon, 10 Apr 2017 15:44:16 GMT

I inserted what you wrote. It did not work. I still get a column and measure I do not want. Do I have to alter the other measure/dimension that it is bringing in erroneously?

=SubStringCount(Concat('|' & _dimension & '|', ','), '|Year Built|')

=SubStringCount(Concat('|' & _measure & '|', ','), '|Account Month Issues|')

Re: Inline Load with Spaces

sunny_talwar — Mon, 10 Apr 2017 15:46:18 GMT

What is your conditional statements for Issue and Accounting Year? I am guessing you would not want to see them because they are not selected, right?

Re: Inline Load with Spaces

sunny_talwar — Mon, 10 Apr 2017 15:48:11 GMT

Accounting Year should be like this (assuming Year means Accounting Year)

=SubStringCount(Concat('|' & _dimension & '|', ','), '|Year|')

Issues:

=SubStringCount(Concat('|' & _measure & '|', ','), '|Issues|')

Re: Inline Load with Spaces

carlcimino — Mon, 10 Apr 2017 15:50:44 GMT

Correct. I answered my own question by taking your suggested expressions and using them in the other fields. Seems picky but it makes sense. Thank you for your help.

=SubStringCount(Concat('|' & _dimension & '|', ','), '|Year|')

=SubStringCount(Concat('|' & _dimension & '|', ','), '|Year Built|')

=SubStringCount(Concat('|' & _measure & '|', ','), '|Issues|')

=SubStringCount(Concat('|' & _measure & '|', ','), '|Account Month Issues|')