Hi
I am quite new to this so please forgive the ignorance.
The objective is to be able to report on the latest statuses of process of product (bond registration) .
These products are linked to Persons. They appear in the Bonds table and Products (bond has more detail)
The process/statuses has 3 dimensions.(contained in the product process table( there is also are ref sheet showing the parent/child relationships called child ref.
1 Bond Process (process name)
2.Start
2.1 create date
2.2 Instruction receive by attorney...
3.Consent docs and bank
3.1 Client Contacted
3.2 Form and letter Received back from Customer
3.3 Consent letter sent to Bank with form and or letter
3.4 Consent Received
3.5 Bank appoints supervising Attorney
3.6 Title and Non Prejudice received
and so see sheet entitled (childref)
And so on you will see this coming through in the script, I tried to rename the conventions accordingly when loading from the processes table
LOAD id as product_process_id,
`parent_process`,
if(Len(Trim(parent_process))=0,'grandparent',if(parent_process=1,'parent','child')) as relation,
if(match(id, '3','4'),'Start')
&if(match(id,'6','7','8','9','10','11'),'Consent Docs & Bank')
&if(match(id,'13','14','15','16','17'),'Prep-Attorney/Client')
&if(match(id,'19','20'),'Pre-Lodge')
&if(match(id,'22'),'Cancellations & Delay')
&if(match(id,'24','25'),'Lodge')
&if(match(id,'27','28'),'Re-Lodge')
&if(match(id,'31','32'),'Registration')
&if(match(id,'34','35','36','37'),'Cancellations & Delay') as Parent,
dependant,
`product_type`,
name as product_process_name,
description as product_process_description,
priority,
`time_period`,
`ext_id`,
deleted;
SQL SELECT *
FROM .`product_processes`;
I load up all the other tables changing id to create primary keys and foreign keys.
You will see there is proposed report tab.
What I was trying to do was use the Bonds, against the baseline complete process(product-processes table) and show the statuses dates against the baseline process per product. I then want to LOOP through the data and work out what the last update process is per dimension.
The logic is that the parent inherits the latest update child ID and date.
so id you looked at dimension one, you could then see the latest updated parent say it was Registration(process id 30) was update by its child(dimension 3) process id 31(registration)
If I looked at the 2nd dimension regsitration I could see that it had inherited its child status id 31 and the date in the status table. Likewise the children who do not have children would also get there own dates in this Latest Status date and latest status id column.
this data would allow for me to track it against the baseline process timeline in process table.
I have been trying everything but cannot seem to get it right, so as last resort I pray for you help before I end up in the nut house.
.png "fernandotoledo"){kind=avatar}
