topic Re: Date join key create synthetic key and loop in database structure in QlikView

Date join key create synthetic key and loop in database structure

elaineng — Mon, 26 Sep 2011 10:26:39 GMT

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?

Re: Date join key create synthetic key and loop in database structure

kaushiknsolanki — Mon, 26 Sep 2011 10:40:52 GMT

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

Re: Date join key create synthetic key and loop in database structure

elaineng — Mon, 26 Sep 2011 10:46:54 GMT

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?

Re: Date join key create synthetic key and loop in database structure

SunilChauhan — Mon, 26 Sep 2011 11:02:13 GMT

please take the attached file

and see the result

hope this may help you

thanks

Sunil Chauhan

Re: Date join key create synthetic key and loop in database structure

elaineng — Mon, 26 Sep 2011 11:13:38 GMT

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

Re: Date join key create synthetic key and loop in database structure

SunilChauhan — Mon, 26 Sep 2011 11:23:12 GMT

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

Re: Date join key create synthetic key and loop in database structure

elaineng — Tue, 27 Sep 2011 00:57:21 GMT

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