Loops in Data Model

[#1]

Hi,

i have Orders joined to some dimension tables and Calendar table, now i have to join my Quotes table to the same dimensions and to Calendar table, this is causing loops in my model. as you can see from the below model i want to join QuoteHeader to TruckSize group and AccountCodes. i have filters for Fiscaldate, AccountCodes, TruckSize and i need to use the same for the quotes too. any help would be greatly appreciated. 

[datanibbler]

Hi,

such circular references are always troublesome. QlikView can resolve them, but only by loosening connections which is not so terribly helpful.

As far as I can see, the table "Trucksizegroup" gets you only one new field? Then you can make that into a small mapping table and use the ApplyMap() function to get that field into your main_table without actually linking or joining them.

That will get you rid of one table. You could in principle do it with larger tables, too - make several mapping tables (one can have only two fields), it is just more effort.

HTH

[#3]

Hi

Please see this expression and check some links below

Applymap() Function

If you want more about this function check this threads below

Mapping Load in Qlikview: A Qlikview Tutorial « Learn Qlikview

http://qlikshare.com/194/

[#4]

Hi DataNibbler,

i have one more question, Can i make TruckSizeGroup and AccountCodes(i only need two fields from this table) as mapping tables and use the ApplyMap() function in QuoteHeader and ConsolidatedTable to get AccountCodeDesc and TruckSizeDesc? and how will the filters act, will they filter the data from both the tables? Sorry, i work on Cognos BI and new to QlikView so bit confused on this.

[datanibbler]

Hi Harry,

with the ApplyMap() function, you can only fetch one field at a time:

- You need a mapping_table (loaded with the prefix MAPPING) that has exactly two fields, where the first one is the value that you pass from your working_table to the mapping_table and the second field is the value that is returned to your working_table
=> If you need two fields, you need to create two mapping_tables from that Truck_table and use the ApplyMap() function twice.

HTH

[#6]

Yes, i will only be using one filed in the fact tables. for example i have Truck Size and AccountCode in both Consolidated table and QuoteHeader i will create a mapping table for "TruckSizeGroup" and "AccountCodes" and use applymap() on both Consolidated table and QouteHeader to get TruckSizeDesc and AccountCodeDesc. when i do this i will have TruckSizeDesc and AccountCodeDesc in both the tables, my question is when i use the TruckSizeDesc and AccountCodeDesc in filters will the data change for both Consolidated Table and QuoteHeader table?

[datanibbler]

Hi Harry,

well, when you have those two fields in both those tables, that will give you a synthetic key between those tables which you probably do not want - though it might be okay, that's not a problem in itself.

That being the case, the two tables will be linked by both of those fields and if you add a listbox of one of those fields to your app, any selection will be replicated in both tables.

If you rename one of the fields in one of the tables to avoid the synthetic key, then your tables will only be linked by one field logically and you could use a trigger so that, upon selection on one field, the same selection will be replicated on another field.

HTH

[#8]

Hi,

Can i concatenate Consolidated table and QuoteHeader in this model, both these fact tables has account codes and TruckSizeGroup. Consolidated table has all orders information and QuoteHeader has all Quotes information. does it make any scene to concatenate these two tables?