Fecthing data from another table based date field in other table

[kesav697921]

I have requirement like based one date filed i need to go back of 2 business working days and need to get the status of that particular date. let me explain with the sample data

Excitement:

Report_Date	Candidate_ID	Status
3/20/2019	12	Low
3/20/2019	13	Low
3/21/2019	12	Med
3/21/2019	13	Low
3/22/2019	12	Med
3/25/2019	12	High
3/25/2019	13	Med
3/26/2019	12	High
3/26/2019	13	Med
3/27/2019	12	Low
3/28/2019	12	High
3/29/2019	12	High

Joinerlist:

Candidate_ID	Strat_Date
12	4/1/2019
13	3/28/2019
14	4/2/2019

Here we need to get details of candidates who was their in Excitement table based on the  Strat_Date field. Note: From Strat_Date we need to go back to the 2 business working days at that we need to get the details of candidate Status.

Ex: for Canidate -12  

Strat_Date was 4/1/2019

so we need to go to the Report_Date =3/28/2019

on this date we need to get Status here it is 'High'

At last we need to get output like this

Strat_Date	High	Med	Low
4/1/2019	1	0	0
3/28/2019	0	1	0
4/2/2019	0	0	0

Kindly help me on this issue.

[pradosh_thakur]

Hi @kesav697921  

When you want to tag someone start with an @ then type the name , for example you wanted to tag sunny so do it like @sunny_talwar  and he will be notified

[pradosh_thakur · Accepted solution]

Coming to your question modify the script similar to below , create a field report_date in the Joiner list table 

load * , if(weekday(Strat_Date)=0 or weekday(Strat_Date)=1,date(Start_Date-4), date(Start_Date-2)) as report_date 

Then you can join/ associate both the table using them and get it in your required format. Try and let me know.

-Pradosh

[PrashantSangle · Accepted solution]

USe below logic
In script
Excitement:
Load Report_Date&Candidate_ID as key,Status Inline [
Report_Date, Candidate_ID, Status
3/20/2019, 12, Low
3/20/2019, 13, Low
3/21/2019, 12, Med
3/21/2019, 13, Low
3/22/2019, 12, Med
3/25/2019, 12, High
3/25/2019, 13, Med
3/26/2019, 12, High
3/26/2019, 13, Med
3/27/2019, 12, Low
3/28/2019, 12, High
3/29/2019, 12, High
];

candidate_id:
Load Start_Date,Date(Num(Start_Date)-3)&Candidate_ID as key Inline [
Candidate_ID, Start_Date
12, 4/1/2019
13, 3/28/2019
14, 4/2/2019
];

In front table
Take Straight Table
Dimension : Start_Date
Expression
1: High >Count({<Status={'High'}>}Status)
2: Med > Count({<Status={'Med'}>}Status)
3: Low > Count({<Status={'Low'}>}Status)

Regards,
Prashant Sangle