Date join key create synthetic key and loop in database structure

[elaineng]

Dear All,

I've an issue with joining the date in the database

Basically, there are 4 mains tables:

All: PDID, Date, Value

WorkProgress: PhaseID, Date

CMMapping : PDID, PhaseID

Calendar: Date, Year, Month,...

Others details table

PhaseDetails: PhaseID, Phasename, ...

PrjDetails: PDID, PrjName, ...

When I load my script, the QV prompted the database has loop structure and create synthetic key.

Any idea how to resolve this issue?

[kaushiknsolanki]

Hi,

     Just rename the date field of WorkProgress and it will solve your problem.

     Or rename the date field of All. Now it depends on you which you want to consider.

Regards,

Kaushik Solanki

[elaineng]

Dear Kaushik,

By renaming the field in WorkProgress or Date field of All. When I select Year, Month, the data of WorkProgress will not select accordingly? How can I overcome this situation?

[SunilChauhan]

please take the attached file

and see the result

hope this may help you

thanks

Sunil Chauhan

[elaineng]

Thanks Sunil Chauhan for the Quick Respond. But after rename "date" field. When I choose Year or Month, the WorkProgress result will not reflect accordingly.

May I know how should I overcome this issue.

Thx

[SunilChauhan]

add the below script in place of  workprogress table and then see

[WorkProgress]:

LOAD BillEventID,

     BillSchemeID,

     ChargePercent,

     CertifiedDate as 'Date',

     PhaseID as PhaseID1,

    Seqno,

    UnitID,

    UnitStatus;

SQL SELECT BillEventID, BillSchemeID, CertifiedDate, ChargePercent, PhaseID,

    Seqno, UnitID, UnitStatus,PDID

FROM "SCITY_MY_INTERDB".dbo.WorkProgress a

RIGHT JOIN "SCITY_MY_INTERDB".dbo.CMdbMappingIFCA b ON a.PhaseID = b.IFCAPhaseID

WHERE CertifiedDate IS NOT NULL;

and let me know if you have any concern

[elaineng]

Dear Sunil Chauhan,

After rename PhaseID to PhaseID1, same issue happen, when I choose the particular project. The work progress information is not reflect accordingly.

Actually, "All" table is similar to the target and the "WorkProgress" table is the actual result. I need to compare the target and actual result of the same project for the particular range

Thank you