How to compare the data in row-wise manner between 2 dates for the same element id (Date1 - let say, current date, Date2 - any history date).

[#1]

I have explained the necessary details in the attachment. The output needs to be developed in Pivot table in the same format as in excel.

Thanks in advance

[datanibbler]

Hi Sonia,

you can go about this in two ways, either in the script or on the GUI:

- In the script, you have to sort your table first and then you can use the PEEK() or PREVIOUS() functions to set
   some flag to tell you when the status is different (and certain fields are the same)

- On the GUI, you could (load a Line_ID with RowNo()) and then use set_analysis, but if you're not comparing to the
   line immediately above, that's going to be a bit more tricky

=> I would suggest the flag in the script.

HTH

Best regards,

DataNibbler

[sunilkumarqv]

Like these

[Anonymous]

Try this. It works on my end. Here is the script tab:

BaseTable:

LOAD * INLINE [
    Date, ID, Value, Status1
    Date 1, 1, xx, Done
    Date2, 1, yy, Done
    Date 1, 2, fef, Proposed
    Date2, 2, dfdf, Done
    Date 1, 3, fdf, Draft
    Date2, 4, csdc, Done
];

NewTable:
LOAD *,
if(Status1=peek(Status1, RowNo(), 'BaseTable'), 1,0) as StatusFlag
Resident BaseTable Order By ID;

DROP BaseTable;

Now in your straight table, input the statement below as shown in the image. Just replace color with whatever color you need.

if(sum(total <ID>StatusFlag)=0,argb(50,0,0,255))

[maxgro]

in background color I used the expression

if( not concat(total <ID> Status)= Status & Status, rgb(220,230,241))

to get

[#6]

Hi Sunil !

But I believe that you have changed the bg color with ID as reference . .But my requirement doesn't work that way . .

Thanks for your efforts anyways .. 

[#7]

Thanks this worked well to a certain extent . . 
But is there a way to hide the StatusFlag column ?

I know this logic works on this column, but is there a way to delete this column and have another work around for the same?

[CELAMBARASAN · Accepted solution]

You can do this without using Status Flag. Use the below expression as Background color expression

if(Count(total <ID> Distinct Status)>1, argb(50,0,0,255))

[#9]

not concat?

I am not sure how this works . .

I tried using 'no concat' instead of 'not concat' but i am still not able to get the desired result

[#10]

This is perfect !

Thank you so much !