7 Replies Latest reply: Oct 1, 2014 7:49 PM by Jonathan Poole RSS

    trouble with cyclic loop

    Dhirendra Diwan

      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.

        • Re: trouble with cyclic loop
          Jonathan Poole

          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

            • Re: Re: trouble with cyclic loop
              Dhirendra Diwan

              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