topic Re: spaces in data is throwing me off in QlikView

spaces in data is throwing me off

becki_kain — Wed, 25 Nov 2020 16:16:04 GMT

I have data coming in with "_" in the name, like P_201709 and when i run a calculation like this, in the variables section:

total_sales_volume_tp1=sum({1<[TP]={$(TP1)}>} TOTAL Volume)

the field "Volume" is summed up just fine.

now, when the data field looks like this "2017 Q3", the same calculation is blank. looking at the raw data, the field "Volume" does have numerical data in it but it seems that any row which has a space in the time period field has no volume to sum up. i tried this:

=sum({1<[TP]={$("TP1")}>} TOTAL Volume) and that not only didn't solve the problem, it broke all the other rows. any ideas, please? thanks

becki kain

Re: spaces in data is throwing me off

vishsaggi — Tue, 14 Nov 2017 19:26:36 GMT

Need more information with some sample data!

Re: spaces in data is throwing me off

becki_kain — Tue, 14 Nov 2017 19:50:03 GMT

excel and qlik enclosed

Re: spaces in data is throwing me off

vishsaggi — Tue, 14 Nov 2017 20:00:27 GMT

Try single quotes like

= sum({1<[TP]={ '$(TP1)'}>} TOTAL Volume)

Re: spaces in data is throwing me off

Anonymous — Tue, 14 Nov 2017 23:03:43 GMT

change your TP1 variable to

= '[' & Subfield(GetFieldSelections(TP, ','), ',', 1) & ']'

Re: spaces in data is throwing me off

becki_kain — Wed, 15 Nov 2017 13:39:03 GMT

no, that didn't help but thanks. I tried both single quotes and it didn't help where the spaces were and broke the underscores. thanks

Re: spaces in data is throwing me off

becki_kain — Wed, 15 Nov 2017 13:48:14 GMT

that works but then what do I do for the caption of the box:

=TP1 &' ' &'Retail Share '&TP2

now TP1 is [2016 Q2] not 2016 Q2 and people are fussy about their captions. how do I split the [] off, for the caption? thanks

Re: spaces in data is throwing me off

vishsaggi — Wed, 15 Nov 2017 14:58:20 GMT

What is your expected output based on the file you enclosed? Can you send us the output you are looking for?

Re: spaces in data is throwing me off

Anonymous — Wed, 15 Nov 2017 16:13:22 GMT

You could either create duplicate variables, one with [] and one without OR

you'd have to do

=prugechar(TP1 &' ' &'Retail Share '&TP2,'[]')

Re: spaces in data is throwing me off

becki_kain — Wed, 15 Nov 2017 16:17:48 GMT

purgechar! thanks! i'm still learning the huge amount of commands there are, for qlik