topic Re: set analysis with 2 different data types in QlikView

set analysis with 2 different data types

kmswetha — Mon, 30 Oct 2017 09:14:30 GMT

Hi,

I'm having a set analysis with alternate sates where I have show data of 2 different data types, Both number and string

Here is what i'm using,
MaxString({[state1]<field1={1}>}value)+sum({[state1]<field1={2}>}value)
since data is of 2 different data types set analysis is not performing OR operation.

Other way i'm trying is using if condition but not sure how to use alternate states in if condtion.

Any suggestions?

Re: set analysis with 2 different data types

tresB — Mon, 30 Oct 2017 09:19:38 GMT

Adding a string to a number? ! What are you really trying to achieve? Could you explain with an example may be?

Re: set analysis with 2 different data types

avinashelite — Mon, 30 Oct 2017 09:20:52 GMT

Share the sample app so that we could understand problem better

Re: set analysis with 2 different data types

Mark_Little — Mon, 30 Oct 2017 09:31:20 GMT

Hi Swe,

Could you please give a little more details, It is hard to give advise when we don't know exactly what it is you are doing and what you are trying to achieve. A real world example would be a good start, some example data and with explanation of what you are trying to do and your expected result.

Mark

Re: set analysis with 2 different data types

kmswetha — Mon, 30 Oct 2017 09:32:37 GMT

my value field has a special character some times, which cannot be displayed if I Used SUM, so would need a string function to display the special character.

My Value field has both number and string

Re: set analysis with 2 different data types

Mark_Little — Mon, 30 Oct 2017 09:37:53 GMT

HI,

I would look at the purgechar() function to remove the special char, then wrap in a num function if needed.

NUM(Purgechar(Value,'!')) - Replace ! with the special char you mentioned.

Then you are adding two numbers.

Mark

Re: set analysis with 2 different data types

kmswetha — Mon, 30 Oct 2017 09:39:54 GMT

Hi Mike,

This is the example below

load * Inline [
F1, F2.F3
abc, 9*,1
def, 10,2
ab, 100,2
];

Since F2 has special character I cant directly sum, I've to use something like this if(right(F2,1)='*',maxstring(F2),sum(F2)) .

But the problem now is alternate states, how to put the above logic with alternate statues, In if clause we cannot use alternate states directly so I used something like this

MaxString({[state1]<F3={1}>}value)+sum(F3={2}>}value). This doesn't work as + wouldn't work with 2 different types

Re: set analysis with 2 different data types

avinashelite — Mon, 30 Oct 2017 09:42:54 GMT

If that is the case , create a new column without the special character and use that column for sum etc and while displaying use the other column that should resolve your issue

Re: set analysis with 2 different data types

kmswetha — Mon, 30 Oct 2017 09:44:47 GMT

wanted to display with special character

Re: set analysis with 2 different data types

Mark_Little — Mon, 30 Oct 2017 10:00:50 GMT

Like you say you aren't going to be able to some a num and string.

I would still look at using numbers and maybe make some changes in the load script. Something like below

DATA:
load * Inline [
F1, F2.F3
abc, 9*,1
def, 10,2
ab, 100,2
];

NoConcatenate

Ordered:
Load
F1,
F2,
F3,
NUM(PurgeChar(F2,'*')) AS F2_SUM,
IF(RIGHT(F2,1)='*',1,0) AS Special
Resident DATA;

Drop Table DATA;

Then you will just be dealing with numbers and no if's, but you are still aware when you have special characters with the Special flag.

Mark

Re: set analysis with 2 different data types

kmswetha — Mon, 30 Oct 2017 10:04:30 GMT

I did that mark, But you cannot concatenate in front end right? that ends up again as two different data types

Re: set analysis with 2 different data types

Mark_Little — Mon, 30 Oct 2017 10:46:20 GMT

Hi,

IF you do your calculation as normal with the numbers and ignore the special char, once you are happy with the result you can add an if to your set analysis like

'Your Set analysis'&IF(SUM(Special)= 1,'*')

Or even have a different way to visualise it, with a coloured text or field.

Re: set analysis with 2 different data types

kmswetha — Mon, 30 Oct 2017 10:59:39 GMT

I tried even concatenating.

I used something like

(sum({[state1]<F3={1}>}value1)&'*')

sum(F3={2}>}value).

Even this is not working

Re: set analysis with 2 different data types

Mark_Little — Mon, 30 Oct 2017 11:09:13 GMT

HI,

You won't be able to sum a string and num. The only way it will work is to do you calculation first and then add the special character on afterwards. So above would be something like

(sum({[state1]<F3={1}>}value1)

sum(F3={2}>}value)&'*')

Mark

Re: set analysis with 2 different data types

kmswetha — Mon, 30 Oct 2017 11:12:41 GMT

The one you have written will append for entire expression. * has to come up only when F3 is 1. I cannot append * for entire expression. Would need only for this expression

sum({[state1]<F3={1}>}value1)&'*'

Re: set analysis with 2 different data types

sunny_talwar — Mon, 30 Oct 2017 11:15:48 GMT

For this data that you have provided

load * Inline [
F1, F2.F3
abc, 9*,1
def, 10,2
ab, 100,2
];

What should the final output look like?

Re: set analysis with 2 different data types

Mark_Little — Mon, 30 Oct 2017 11:18:32 GMT

Hi Swe,

Yes it would I was just using your example, but as I was trying to explained in the pervious post, if you flag the fields in the data that have a special character you can then us an if statement to add the * back in where need

(sum({[state1]<F3={1}>}value1)

sum(F3={2}>}value)& IF(Special= 1,'*')

you may need something like IF(ONLY(Special)= 1,'*')

Re: set analysis with 2 different data types

kmswetha — Mon, 30 Oct 2017 11:20:32 GMT

Hi Sunny,

Something like this. Final output filed will have both number and strings

Re: set analysis with 2 different data types

sunny_talwar — Mon, 30 Oct 2017 11:22:59 GMT

So if the data would have looked like this

load * Inline [
F1, F2.F3
abc, 9*,50
def, 10,2
ab, 100,2
];

then it would be 450* for abc? If that is true... may be you need this

KeepChar(F2, '0123456789') * F3 & PurgeChar(F2, '0123456789')

Re: set analysis with 2 different data types

kmswetha — Mon, 30 Oct 2017 11:30:15 GMT

No.

There are alternate states too.

load * Inline [
F1, F2.F3,F4,
abc, 9*,1,9
def, 10,2,10
ab, 100,2,100
];

F3 field tells if there is a special character and F4 is removing the special character

Since there are alternate states I cannot use if condition

sum({[state1]<F3={1}>}F4)

sum(({[state2]<F3={2}>}F2)

Now till hear i'm able to display without * by converting to number.

Now if i have to concatenate * to the above expression, is it feasible?I have to concatenate to the first statement.

Which means sum({[state1]<F3={1}>}F4)