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.
Please help me on this.
Sorry again for not posting an image.
