Data modeling and association of data

[ritumishra01]

so i have three table idea demand and project , and once idea submitted it changes to demand and then to project so i want all the data sync and i want no duplictes in the sheet. it should consider the lates status like if idea is changed to demand and then to project it should give us the recent start by project date not the idea or demand date and if status is completed for idea but in progress for demand it should give us that status for demand not idea.

while i apply join to combine all the table i could see duplicte demand and duplicate project

Idea:
LOAD
idea_number,
// dmnd_number,
idea_sys_id,
idea_sys_created_by,
user_name,
idea_short_description,
idea_u_business_area_group,
idea_u_business_area,
idea_state,
if(idea_state = '1' or idea_state = '2' or idea_state = '-5','Submitted' ,
if(idea_state = '3','Completed',
if(idea_state ='7','rejected'))) as Status,
idea_sys_created_on,
dmnd_sys_created_on as idea_dmnd_sys_created_on
FROM [lib://Intake Dashboard Files/idea_demand_01.QVD]
(qvd)
where idea_sys_created_on >= '2023-01-01 00:00:00' ;
//and dmnd_sys_created_on >= '2023-01-01 00:00:00';
outer join (Idea_01)
Demand:
LOAD
dmnd_number,
// prj_number,
dmnd_state,
dmnd_start_date as start_date,
if(dmnd_state = '1' or dmnd_state = '2' ,'submitted' ,
if(dmnd_state = '9' ,'completed',
if(dmnd_state ='3' or dmnd_state = '-4','under_review',
if(dmnd_state = '8','in_progress',
if (dmnd_state ='7' or dmnd_state = '10' or dmnd_state = '5','rejected')))))as Status,
u_opid_id_dmnd,
dmnd_sys_created_by,
__KEY_result
FROM [lib://Intake Dashboard Files/demand_project_01.QVD]
(qvd)
where dmnd_sys_created_on >= '2023-01-01 00:00:00';
//and prj_sys_created_on >= '2023-01-01 00:00:00';

outer join (Idea_01)
Project:
LOAD
prj_number,
prj_state,
prj_start_date as start_date,
prj_end_date as end_date,
sys_created_on as project_sys_created_on,
__KEY_result,
u_opid_id_prj,
if(num(prj_state) = 1 or num(prj_state) = 2 or num(prj_state) = -5,'in_progress',
if(num(prj_state)= 4 or num(prj_state)= 7 or num(prj_state) =15 ,'rejected',
if(num(prj_state) = 3,'completed'))) as Status

FROM [lib://Intake Dashboard Files/project_01.QVD]
(qvd)
where sys_created_on >= '2023-01-01 00:00:00';

Sys_USer:
LOAD
u_opid_id,
user_name,
test_username,
sys_id,
first_name,
last_name,
full_name,
//sys_created_on,
email
FROM [lib://Intake Dashboard Files/Sys_User_01.QVD]
(qvd);

[PrashantSangle]

Your scripts look ok. Except I observed that you comment key field in your script. Any specific reason for that? 

Also, Can you explain you issue with Sample data?

[ritumishra01]

I am getting duplicate records for demand and project .

how to get rid of duplicate records abd get the most recent status out of all the three table and most receng start and end date

[PrashantSangle]

without data difficult to predict  the solution.

[woutermak]

For so far I can see, you are use a join only on status. I you only want to have one record, you need to join on another field.