- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
in one day but not another
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
];
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Many thanks Sergey!