in one day but not another

[Anonymous]

Is there an elegant way to show  change in names between 2 days.

Displaying 1) new added names between last date and the date before last

                  2) dropped names between last date and the date before last

for example for

New in the last day compare to the day before:

C

D

E

Dropped on the last day compare to day before:

F

G

Thank you.

Raphael

LOAD * INLINE [

    Name, Date, Score

    A, 01/01/2018, 1

    B, 01/01/2018, 2

    C, 01/01/2018, 3

    D, 01/01/2018, 4

    E, 01/01/2018, 5

    A, 02/01/2018, 6

    B, 02/01/2018, 7

    F, 02/01/2018, 8

    G, 02/01/2018, 9

    A, 03/01/2018, 10

    B, 03/01/2018, 11

    C, 03/01/2018, 12

    D, 03/01/2018, 13

    E, 03/01/2018, 14

];

[Sergey_Shuklin · Accepted solution]

Hello, Raphael!

You'll have to do some script manipulating:

The script:

dates_table: //sorted dates table

LOAD distinct Date Resident main Order by Date asc;

let max_date=Peek('Date',-1,'dates_table');

let before_max_date=Peek('Date',-2,'dates_table');

left join (main)

LOAD Distinct '$(before_max_date)' as Date, Name, 1 as flag1 Resident main

Where Date='$(max_date)';

left join (main)

LOAD Distinct '$(max_date)' as Date, Name, 1 as flag2 Resident main

Where Date='$(before_max_date)';

result_tab:

LOAD Date, Name, Score

, if(Date = '$(before_max_date)',

if(flag1=1,'save','left')

, if(Date = '$(max_date)',

if(flag2=1,'save','new')

)) as flag

Resident main;

Just place it after your Inline part.

And, I also attaching a file!

Hope that's what you're lookin for!

[Anonymous]

Many thanks Sergey!