Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
elaineng
Contributor
Contributor

Date join key create synthetic key and loop in database structure

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?

6 Replies
kaushiknsolanki
MVP & Luminary
MVP & Luminary

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
elaineng
Contributor
Contributor
Author

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?

TigerisBack
Champion
Champion

please take the attached file

and see the result

hope this may help you

thanks

Sunil Chauhan

elaineng
Contributor
Contributor
Author

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

TigerisBack
Champion
Champion

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
Contributor
Contributor
Author

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