Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor

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
Highlighted
MVP
MVP

Re: set analysis with 2 different data types

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

Highlighted

Re: set analysis with 2 different data types

Share the sample app so that we could understand problem better

Highlighted
Partner
Partner

Re: set analysis with 2 different data types

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

Highlighted
Contributor

Re: set analysis with 2 different data types

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

Highlighted
Partner
Partner

Re: set analysis with 2 different data types

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

Highlighted
Contributor

Re: set analysis with 2 different data types

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

Highlighted

Re: set analysis with 2 different data types

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

Highlighted
Contributor

Re: set analysis with 2 different data types

wanted to display with special character

Highlighted
Partner
Partner

Re: set analysis with 2 different data types

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