Data inconsistency when using duplicate tables for removing circular loops
Sorry for not attaching any block diagram to get a complete idea. I tried to attach an image but i am unable to.
I will try to detail my issue
NB: This is a sample case as i cannot post the real data here. The real scenario is a bit more complex.
I have 4 tables.
Sales :
Product ID
Calendar_Date
... and some other fields specific to sales
Product :
Product ID
Calendar_Date
... and some other fields specific to product
Offer:
Product ID
Calendar_Date
... and some other fields specific to Offer
Time :
Processed_Date
... and some other fields specific to Time
I need to associate the tables using the following conditions.
Sales.Calendar_Date=Time.Processed_Date
product.Calendar_Date=Time.Processed_Date
Offer.Calendar_Date=Time.Processed_Date
Sales.Product_ID=Product.Product_ID
Product.Product_ID=Offer.Product_ID
Offer.Product_ID=Sales.Product_ID
In order to achieve this by avoiding circular loops, i have done the following changes.
SALES
Product ID1
Calendar_Date as Calendar_Date1
PRODUCT
Product ID1
Product ID2
Calendar_Date as Calendar_Date2
OFFER
Product ID2
Product ID3
Calendar_Date as Calendar_Date3
TIME1
Processed_Date as Calendar_Date1
TIME2
Processed_Date as Calendar_Date2
TIME3
Processed_Date as Calendar_Date3
I could avoid circular loops by doing so. But the issue is,
When i try to create a chart or table in qlik, i can only get the correct data if i plot between time1 and Sales, but note for time2 vs Sales or time3 vs Sales.
Similarly for time1 vs product or time3 vs product and time1 vs Offeror time2 vs Offer.
