Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
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
Employee
Employee

Re: Re: Re: trouble with cyclic loop

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

7 Replies
Employee
Employee

Re: trouble with cyclic loop

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

Re: Re: trouble with cyclic loop

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

Employee
Employee

Re: trouble with cyclic loop

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

Re: Re: trouble with cyclic loop

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

Employee
Employee

Re: Re: Re: trouble with cyclic loop

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

Re: trouble with cyclic loop

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

Employee
Employee

Re: trouble with cyclic loop

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