trouble with cyclic loop

[#1]

Hello, I have to solve attached problem, where I need to design ZIP-ZIP rates and State-State rate if ZIP-ZIP rate not available. Also user need to enter any ZIP code as Origin and any ZIP as destination. Logic should automatically check if ZIP rate is available else associate State rates for that combination. I created attached solution, but qlikview is giving me circular loop. Origin and Destination Geo tables have all 40K zip codes in US with respective state. I need your help to create solution to either search for ZIP-ZIP (Point-Point) if available or State2State rate. I tried making Origin-Destination ZIP key but as your know 40K * 40K gives me more than a Billion possible combinations and hence need better solution to make either Link table or better data model.

[JonnyPoole]

Can you post a small sample of zip-zip rates as well as  state-state rates for some zip combos not represented? A zip-state mapping for the non-represented zip-zips will help too .  That will help global community users get a better idea.

Nothing more than a few rows, should be enough to create a QVW sample.

Loop joins won't work but SET ANALYSIS, data islands,  alternate states or all of the above will likely yield a solution

[#3]

Hello Jonathan Poole

Thank you for your reply. Attached is the sample data set.

If user enters Origin Zip 74522 and Destination Zip 74538, it should return values from point to point table as this combination exists in that table. Lets say, if user enters 72521 to 90001, which does not exists in point to point table but since according to Geo table these zips are in AR to CA and this combination is in state-state table, it should return value from state table. if not exists in both tables it should return NULL value. Attached is just few sample lines, but as there could be 40K zip codes in geo table, and it is only used to capture state (if point to point) rate is not there, please help to design optimum data model without creating all possible combinations of origin-destination zip from geo table.

Really appreciate your help!!

Thanks!

Dhirendra

[JonnyPoole]

Hi do you have any other dimensions ?  To keep data volumes low i am thinking of a 2-3 disconnected data islands as the associative need is quite low.  Is their any other dimensional context needed ? Can you post your existing data model that shows all the other fields you need ?

[#5]

Hello Jonathan,

Thanks again for your reply! Attached is my data model, I do not have any other dimensions. The only requirement is as defined above to get rates from Zip to Zip or State to state.

Thanks!!

[JonnyPoole · Accepted solution]

I used alternate states , two data islands , and some variables to keep data volumes down. '

You select a source and destination zip on the left, and if there is a ZIPtoZIP rate it shows (not current selection) in middle top.

It will also show the statetostate rate in middle bottom.

charts on far right show all possible combinations in the data ... just used for testing purposes

[#7]

Thank you so much Jonathan!!!!! This is the perfect solution.

[JonnyPoole]

Great.   The list of zips i compiled from the state/zip file as well as the ziptoziprate file.  In your scenario, just load all 40,000 zips in there..