Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Comparing two table fields in expression

Hi Experts,

How can I compare two table fields in an expression i.e. DocDate and DOCreateDate. Following expression does not work:

Sum( {<Month(DocDate) <> Month(DOCreateDate)>} RowTotalSO)

Help required!

Regards

8 Replies

Re: Comparing two table fields in expression

Try the below expression

Sum(If(Month(DocDate) <> Month(DOCreateDate), RowTotalSO))

Re: Comparing two table fields in expression

Or may be this:

Sum({<RowTotalSO = {"=Month(DocDate) <> Month(DOCreateDate)"}>} RowTotalSO)

Not applicable

Re: Comparing two table fields in expression

Thanks for reply Sunny,

a quick question:

Sum({<RowTotalSO = {"=Month(DocDate) <> Month(DOCreateDate)"}>} RowTotalSO)

Q: you are comparing RowTotalSO =  {"=Month(DocDate) <> Month(DOCreateDate)"} , can you please tell me what is the logic in this?

Re: Comparing two table fields in expression

What I am doing here is comparing a field to a search string which should either give true or false. So if you were to build a straight table with

Dimension

RowTotalSO

Expression

=Month(DocDate) <> Month(DOCreateDate)

All those rows where you see -1 (true) are the rows which will be included in the expression we were using

(Sum({<RowTotalSO = {"=Month(DocDate) <> Month(DOCreateDate)"}>} RowTotalSO)) and all those rows where the expression gives 0 will not be included.

Now the catch here is that RowTotalSO needs to uniquely determine DocDate and DOCreateDate, because if it doesn' then the search won't get evaluated as true or false. For instance, if you have data like this

RowTotalSO,     DocDate,     DOCreateDate

100,     10/20/2016,     11/20/2016

100,     11/20/2016,     11/25/2016

Now when we will use RowTotalSo as dimension and Month(DocDate) as expression we will have two possiblilties here Oct or Nov and since there are two options, you will see null and null cannot be compared to anything else. So this will fall through.

I hope this helps

Best,

Sunny

Not applicable

Re: Comparing two table fields in expression

Great, I like it. thanks

MVP & Luminary
MVP & Luminary

Re: Comparing two table fields in expression

Hi,

If both the date fields are in same table then better derive a flag in the script itself like below

LOAD

*,

If(Month(DocDate) <> Month(DOCreateDate), 1, 0) AS Flag

FROM DataSource;


Now you can simple use below expression

Sum({<Flag={1}>}RowTotalSO)


This is much faster than doing the front end.


Hope this helps you.


Regards,

Jagan.



Not applicable

Re: Comparing two table fields in expression

Hi Jagan,

It is workable and appropriate indeed, I have question that how can I compare these two date fields i.e.

  1. DocDate
  2. DOCreateDate

Since these two field are residing in two different table. If I store them in a QVD and reload them from this recent crated QVD, it does not load the with the fields merged. If it does so it would been much easier and accurate to implement your idea.

Please guide how can I proceed by storing these two loaded table in one QVD and then while reloading again I can use Flag.


Re: Comparing two table fields in expression

You will need to join them before saving them into a qvd file.