Announcements
[WEBINAR] Accenture & Qlik: Accelerating BI Migration to SaaS with Qlik on Dec 13th: REGISTER
cancel
Showing results for
Did you mean:
Creator

## set analysis with 2 different data types

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?

20 Replies
MVP

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

MVP

Share the sample app so that we could understand problem better

Master

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

Creator
Author

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

Master

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

Creator
Author

Hi Mike,

This is the example below

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

MVP

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

Creator
Author

wanted to display with special character

Master

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:
F1, F2.F3
abc, 9*,1
def, 10,2
ab, 100,2
]
;

NoConcatenate

Ordered:
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

Community Browser