Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I want if total count of primary key is less than total count of Primary key of Previous quarter then mark that row with Green else red.
suppose, if
Date | Quarter | Primary Key |
01-01-2016 | Q1 16 | abc |
02-01-2016 | Q1 16 | abc |
03-01-2016 | Q1 16 | abc |
04-01-2016 | Q2 16 | pqr2 |
05-01-2016 | Q2 16 | pqr3 |
06-01-2016 | Q2 16 | pqr4 |
26-04-2016 | Q2 16 | pqr5 |
27-04-2016 | Q2 16 | pqr6 |
28-04-2016 | Q2 16 | pqr7 |
29-04-2016 | Q2 16 | xyz |
30-04-2016 | Q2 16 | xyz |
01-05-2016 | Q2 16 | xyz |
29-10-2016 | Q4 16 | xyz |
30-10-2016 | Q4 16 | xyz |
31-10-2016 | Q4 16 | mno |
01-11-2016 | Q4 16 | mno |
02-11-2016 | Q4 16 | mno |
should return
01-01-2016 | Q1 16 | abc | Green |
02-01-2016 | Q1 16 | abc | Green |
03-01-2016 | Q1 16 | abc | Green |
04-01-2016 | Q2 16 | pqr2 | red |
05-01-2016 | Q2 16 | pqr3 | red |
06-01-2016 | Q2 16 | pqr4 | red |
26-04-2016 | Q2 16 | pqr5 | red |
27-04-2016 | Q2 16 | pqr6 | red |
28-04-2016 | Q2 16 | pqr7 | red |
29-04-2016 | Q2 16 | xyz | red |
30-04-2016 | Q2 16 | xyz | red |
01-05-2016 | Q2 16 | xyz | red |
29-10-2016 | Q4 16 | xyz | Green |
30-10-2016 | Q4 16 | xyz | Green |
31-10-2016 | Q4 16 | mno | Green |
01-11-2016 | Q4 16 | mno | Green |
02-11-2016 | Q4 16 | mno | Green |
Thanks in Advance.
Data:
LOAD Date,
Quarter,
[Primary Key]
FROM
[Data1.xlsx]
(ooxml, embedded labels, table is Sheet1);
Count:
LOAD Quarter,
Count([Primary Key]) as Countkey
Resident Data
Group by Quarter
Order by Quarter;
Left Join(Data)
LOAD *,
if(RowNo()=1 or Countkey<Previous(Countkey),'Green','Red') as Staus
Resident Count;
DROP Table Count;