Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

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

1.bmp

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

];

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Hello, Raphael!

You'll have to do some script manipulating:

script.png

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!

View solution in original post

2 Replies
Highlighted
Specialist
Specialist

Hello, Raphael!

You'll have to do some script manipulating:

script.png

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!

View solution in original post

Highlighted
Creator III
Creator III

Many thanks Sergey!