Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

trouble with cyclic loop

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.

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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

Capture.PNG.png

Capture.PNG.png

View solution in original post

7 Replies
JonnyPoole
Employee
Employee

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

Not applicable
Author

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
Employee
Employee

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 ?

Not applicable
Author

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
Employee
Employee

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

Capture.PNG.png

Capture.PNG.png

Not applicable
Author

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

JonnyPoole
Employee
Employee

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..