Comparing two table fields in expression

[#1]

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

[CELAMBARASAN]

Try the below expression

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

[sunny_talwar]

Or may be this:

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

[#4]

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?

[sunny_talwar]

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

[#6]

Great, I like it. thanks

[jagan]

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.

[#8]

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.

[sunny_talwar]

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