Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try the below expression
Sum(If(Month(DocDate) <> Month(DOCreateDate), RowTotalSO))
Or may be this:
Sum({<RowTotalSO = {"=Month(DocDate) <> Month(DOCreateDate)"}>} RowTotalSO)
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?
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
Great, I like it. thanks
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.
Hi Jagan,
It is workable and appropriate indeed, I have question that how can I compare these two date fields i.e.
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.
You will need to join them before saving them into a qvd file.