topic Re: Breaking Circular Loop in QlikView

Breaking Circular Loop

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm new to QlikView. I create dashboard that contain harvesting data.

There are table HarvestAOP; Harvest original plan which is connect to master calendar by week (AgroWeekYear) and table Harvest LF; Harvest Lasted Forecast which connect to master calendar by daily (AgroDate). These 2 tables has exactly match fields except this field (AgroWeekYear or AgroDate -> base on which table)

The problem is qlikview is automatically group match key of those tables and cause to Circular loop, I try to solve this problem by concatenate those table but then my master calendar is broken. Could you please gave me an idea on this? Thanks

PS. English is not my main language, apologized if my writing is hard to understand.

At first My ER looking like this.

And when I concatenate tables, dimension calendar is broken.

I research some solution and see that the most suggestion is create new keys to remove relationship among them. but It didn't work for me (or I'm not doing it right?). It'll be grateful if you can help me solve this problem. (My qvw is attached.)

Re: Breaking Circular Loop

miguelbraga — Wed, 12 Oct 2016 16:02:51 GMT

Hey there,

You can do something like this to improve your data model. Do the following:

Zone:

LOAD *

FROM [your connection to your Zone database goes here];

left join(Zone)

LOAD *

FROM [your connection to your Subzone database goes here];

HarverstAOP:

LOAD *,

'AOP' as type

FROM [your connection to your HarverstAOP database goes here];

Concatenate

HarverstLFSubzone:

LOAD *,

'LFSubzone' as type

FROM [your connection to your HarverstAOP database goes here];

CropYear:

LOAD *

FROM [your connection to your CropYear database goes here];

AgroCalendar:

LOAD *

FROM [your connection to your AgroCalendar database goes here];

Best regards,

D.A. MB

Re: Breaking Circular Loop

miguelbraga — Wed, 12 Oct 2016 16:05:11 GMT

In your case:

[your script]:

HarvestAOP:

//LOAD *,

// AutoNumber(AgroWeekYear) as KeyMapping

//;

LOAD //CropOnlyYear,

//Season,

SubzoneID,

TransType,

//AgroYear,

//AgroWeekOfYear,

AgroWeekOfYear & '-' & AgroYear as AgroWeekYear,

HarvestAOP,

CropWithSeason,

'LFSubzone' as type

FROM

[.\Harvesting_AOP_15_D_20161110.qvd]

(qvd);

////Concatenate

////Outer join

Concatenate

HarvestLFSubzone:

//LOAD *,

// AutoNumber(AgroDate) as KeyMapping ;

LOAD //CropOnlyYear,

//Season,

SubzoneID,

TransType,

AgroDate,

HarvestLFSubzone,

CropWithSeason,

'AOP' as type

FROM

[.\Harvesting_LF_Subzone_15_D_20161110.qvd]

(qvd);

//inner Join

//LOAD AgroWeekYear

//FROM

//[.\Agro_Calendar_20160920.qvd]

//(qvd);

AgroCalendar:

LOAD AgroDate,

CalendarWeek,

CalendarYear,

CalendarMonth,

Day,

CalendarMonthYear,

CalendarWeekYear,

WeekDay,

AgroYear,

AgroMonthName,

AgroMonth,

AgroWeekOfYear,

AgroWeekOfMonth,

AgroMonthYear,

AgroWeekYear

FROM

[.\Agro_Calendar_20160920.qvd]

(qvd);

Zone:

LOAD ZoneID,

ZoneNameEN

FROM

[.\Zone_20160928.qvd]

(qvd);

left join (Zone)

LOAD ZoneID,

SubzoneID,

SubzoneNameEN

FROM

[.\Subzone_20160928.qvd]

(qvd);

CropYear:

LOAD CropOnlyYear,

CropYear,

Season,

'Crop ' & CropOnlyYear & ' ' & Season as CropWithSeason

FROM

[.\11_Crop_Year_20161011.qvd]

(qvd)

WHERE(not IsNull(CropOnlyYear) And not IsNull(CropYear) And not IsNull(Season));

//KEYMAPPING:

//LOAD

//KeyMapping ,

//AgroWeekYear

//Resident HarvestAOP;

//Concatenate

//LOAD

//KeyMapping ,

//AgroDate

//Resident HarvestLFSubzone;

//DROP Field AgroWeekYear From HarvestAOP;

//DROP Field AgroDate From HarvestLFSubzone;

//DROP Field AgroDate from HarvestLFSubzone;

Re: Breaking Circular Loop

Thu, 13 Oct 2016 02:24:21 GMT

Hi miguelbraga ,

I try following your advice but still Harvest LF & AOP not mapping with master calendar as expected... I assume that because in Harvest table now has fields AgroWeekYear & AgroDate but 1 transaction only contain value in one of those fields only. Any Suggestion?