Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Comparing two field in set analysis

Hello Guys,

Can we compare two field in set analysis?

I have seen some earlier posted question but no proper response.

Example: I am having two fields say OpenData, MISDate with Amount.

Can I write some thing in SET like =sum({<OpenDate=MISDate>} Amount)??

I tried with IF, it is working but not with set analysis.

Pls suggest.

Regards,

Balraj Ahlawat

8 Replies
alexandros17
Partner - Champion III
Partner - Champion III

No you cannot, you can use Field = Value where this value can be a specific field single value but not the entire set of values.

Anonymous
Not applicable
Author

may be try:

sum({<Date={"=OpenDate=MISDate"}>}Amount)

Anonymous
Not applicable
Author

try this

sum({<OpenDate={'=MISDate'}>} Amount)

sasiparupudi1
Master III
Master III

Can you please post a sample?

tresesco
MVP
MVP

Try like:

sum({<OpenDate={"=OpenDate=MISDate"}>}Amount)

anderseriksson
Partner - Specialist
Partner - Specialist

This is a case for the P() function in set analysis;

sum({$<OpenDate=P(MISDate)>} Amount)


Match filter field OpenDate on the possible dates acquired from the MISDate field.

But this will match all OpenDate to all MISDate.

If you want only those where OpenDate = MISDate on the exact data post you need a different approach.

I would probably add a field when loading data where I store if the dates are equal and use that field in set analysis.

Kushal_Chawda

sum({<OpenDate={"=OpenDate=MISDate"}>} Amount)

or

sum(if(OpenDate=MISDate,Amount))

or

sum({<OpenDate=p(MISDate)>}Amount)

anderseriksson
Partner - Specialist
Partner - Specialist

Another way of doing that would solve your problem is:

sum( -(OpenDate=MISDate) * Amount)


The comparison yields 0 if uneqal and -1 if equal dates.

Negating that you get 1 for equal dates, 0 for unequal dates

which you then can use as a simple factor in a multiplication.

This does away with the if() function which is notorious for taking

resources when computing and should be avoided if possible.

Doing that I would probably add a comment to the code explaining what it does.

For someone experienced in C-programming it is nothing strange.