topic Re: 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). in QlikView

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).

Fri, 14 Mar 2014 14:08:41 GMT

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

Re: 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).

datanibbler — Fri, 14 Mar 2014 14:18:10 GMT

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

Re: 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).

sunilkumarqv — Fri, 14 Mar 2014 14:38:22 GMT

Like these

Re: 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).

Anonymous — Fri, 14 Mar 2014 15:25:56 GMT

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))

Re: 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).

maxgro — Fri, 14 Mar 2014 15:51:29 GMT

in background color I used the expression

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

to get

Re: 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).

Tue, 18 Mar 2014 05:25:17 GMT

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 ..

Re: 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).

Tue, 18 Mar 2014 05:40:14 GMT

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?

Re: 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).

CELAMBARASAN — Tue, 18 Mar 2014 06:04:19 GMT

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))

Re: 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).

Tue, 18 Mar 2014 07:15:13 GMT

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

Re: 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).

Tue, 18 Mar 2014 07:30:10 GMT

This is perfect !

Thank you so much !

Re: 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).

maxgro — Tue, 18 Mar 2014 07:50:37 GMT

concat works in this way with your data