Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Shubham_Deshmukh
Specialist
Specialist

Need some help for logic

Sample data (only 2 days data will be there):

dateIDValuestatus
25-Jun-19110 
24-Jun-19112 
25-Jun-19220 
24-Jun-19330 

 

Conditions: 

1. When unique ID is there for each date (as per data , ID 2 & 3  )

eg  : fr ID 2, as 24 Jun data (date-1) is present then it should give 'pledge' else 'return'

2.When both dates have same IDs (as per data , ID 1 )

Then it should be return row for maxDate(25 Jun) and exclude minDate(24Jun) of ID with difference of values

Desired o/p : 

datesIDValuestatus
25-Jun-1912return
25-Jun-19220pledge
24-Jun-19330return

 

@johnw  

Labels (4)
1 Solution

Accepted Solutions
udayreddy06
Contributor II
Contributor II

Hello Shubham,

Below is the script. It can be done better by reducing the steps.

Thanks,
Uday

 

Temp:
Load * Inline [
date, ID, Value
06/25/2019, 1, 10
06/24/2019, 1, 12
06/25/2019, 2, 20
06/24/2019, 3, 30
];


Temp3:
Load
date(Max(date),'MM/DD/YYYY') as Date_Temp
Resident
Temp
Group by date;

let vMaxDate = FieldValue('Date_Temp','1');

DROP Table Temp3;


Temp2:
Load
count(ID) as ID_Temp,
ID
Resident
Temp
Group by ID;

Left Join (Temp)
Load
ID,
ID_Temp
Resident
Temp2;

Left join (Temp)
Load
Max(Value)-Min(Value) as Value_Temp,
ID
Resident
Temp
Group by ID;

Left join (Temp)
Load
Max(date) as date,
ID,
'1' as Date_Flag
Resident
Temp
Group by ID;



DROP Table Temp2;

Temp3:
Load
date,
ID,
If(ID_Temp = '2', Value_Temp, Value) as Value,
if(ID_Temp = '2' or date <> '$(vMaxDate)','Return','Pledge') as Status
Resident
Temp
where Date_Flag = '1';


DROP Table Temp;

 

View solution in original post

2 Replies
udayreddy06
Contributor II
Contributor II

Hello Shubham,

Below is the script. It can be done better by reducing the steps.

Thanks,
Uday

 

Temp:
Load * Inline [
date, ID, Value
06/25/2019, 1, 10
06/24/2019, 1, 12
06/25/2019, 2, 20
06/24/2019, 3, 30
];


Temp3:
Load
date(Max(date),'MM/DD/YYYY') as Date_Temp
Resident
Temp
Group by date;

let vMaxDate = FieldValue('Date_Temp','1');

DROP Table Temp3;


Temp2:
Load
count(ID) as ID_Temp,
ID
Resident
Temp
Group by ID;

Left Join (Temp)
Load
ID,
ID_Temp
Resident
Temp2;

Left join (Temp)
Load
Max(Value)-Min(Value) as Value_Temp,
ID
Resident
Temp
Group by ID;

Left join (Temp)
Load
Max(date) as date,
ID,
'1' as Date_Flag
Resident
Temp
Group by ID;



DROP Table Temp2;

Temp3:
Load
date,
ID,
If(ID_Temp = '2', Value_Temp, Value) as Value,
if(ID_Temp = '2' or date <> '$(vMaxDate)','Return','Pledge') as Status
Resident
Temp
where Date_Flag = '1';


DROP Table Temp;

 

View solution in original post

Shubham_Deshmukh
Specialist
Specialist
Author

Thats Great !!! Thanks Uday. Appreciated ur efforts and time.