18 Replies Latest reply: Oct 12, 2012 9:45 AM by Erik Furlanis RSS

    how to create an unique key based on previous values

      Dear guys

       

      I have a sort of a puzzle (for me); for you I think is something really easy!

       

      let say I have a list of items

       

      ItemA
      ItemB
      Chain
      ABKey0001
      DEKey0002
      DFKey0002
      GHKey0003
      LNKey0004
      MNKey0004
      PAKey0001

      I think it speaks for itself, but I will try in my own words.

       

      I'm trying to create, out of a table with two fields (ItemA and ItemB), a new field "Chain" which assigns a Key.

      This Key is generated according to what is the content in ItemA and ItemB.

      If the two fields contain brand new values, then a new Key is created in the Chain field.

      Otherwise, if it in ItemA/B there is an item which is previously in the list, it should assign the Key which was assigned previously.

       

      Is it a hard task?

       

      Thank you!

        • Re: how to create an unique key based on previous values

          I usually hate to bump, but is the question not so clear, or is the problem untackleable?

           

          Thank you!

            • Re: how to create an unique key based on previous values

              I've got somewhere 1 inch further and wrote down this piece of script

               

              LOAD ItemA, 
                   ItemB
              
              FROM
              [\IC_test.xlsx]
              (ooxml, embedded labels, table is Sheet1);
              
              let vMaxRow = NoOfRows('Sheet1'); //get the tot number of rows in source table
              
              For vValueNo=1 to $(vMaxRow) //loop through every row
              
              NewTable:
              LOAD
              ItemA,
              ItemB,
              if(peek('ItemA',$(vValueNo),'Sheet1') <> peek('ItemA',($(vValueNo)-1),'Sheet1'), 'Key'&$(vValueNo), 'SAME') as Chain
              
              RESIDENT Sheet1;
              
              NEXT vValueNo
              
              Drop table Sheet1;
              

               

              expectations: have the script looping around my data (just one field, ItemA, to make it simpler) and check if the previous value in ItemA is the same, input SAME in the Chain field, otherwise create a value as Key&NumberOfTheRecord

               

              result: the script picks up the whole table Sheet1 and attaches a 'Key1' value in the Chain field; then it picks up again the whole table, and attaches a 'Key2' and so forth.

               

              Given my complete lack of experience in programming, I'm quite happy about my result, at least I managed to get a loop started; but still I'm a long way to reach what I want (have the script check current value against ALL the previous values, and if happens to be a new value, assign a new key, else assign the same key as the old value)

               

              Any hint will be much, much appreciated!

                • Re: how to create an unique key based on previous values
                  Adam Abwat-Johnson

                  Hi,

                   

                  I think you're close but when you are creating NewTable, instead of doing Resident Sheet1 do AutoGenerate(1). This will only create one line.

                   

                  You will need to change a few things before this too so it'll look something like this:

                   

                  let vPrevItemA = null();

                  let vPrevItemB = null();

                  let vChain = null();

                   

                  For vValueNo=0 to $(vMaxRow)-1 //Changed this to help with peeks (0 indexed)

                       let vItemA = peek('ItemA',$(vValueNo),'Sheet1');

                       let vItemB = peek('ItemB',$(vValueNo),'Sheet1');

                       if '$(vItemA)'<>'$(vPrevItemA)' then

                       let vChain = 'Key'&$(vValueNo);

                     endif;


                  NewTable:
                  LOAD
                       '$(vItemA)' as ItemA,
                       '$(vItemB)' as ItemB,
                       '$(vChain)' as Chain
                  autogenerate(1);

                   

                  let vPrevItemA = '$(vItemA)';

                  let vPrevItemB = '$(vItemB)';

                   


                  NEXT vValueNo;

                   

                  It's off the top of my head so may need a bit of tinkering with to get it to work!

                   

                  Hope it helps,

                   

                  Chris

                    • Re: how to create an unique key based on previous values

                      Dear Chris,

                       

                      your help was of great value!

                       

                      I really made a gigantic leap and it is starting to get closer to what I want.

                      The first small issue that I have is that if my source table looks like this

                       

                      ItemAItemBKey
                      AAABBBKey0
                      AAACCCKey0
                      EEEFFFKey1
                      AAAHHHKey0
                      LLLMMMKey2

                       

                      it is important that line 4, since it contains AAA which was already in lines 1 and 2, still keeps the same key as the first ones, hence Key0.

                       

                       

                      And then, on a second phase, I will need my check for a new line to be done both on ItemA AND ItemB, by looking back both at ItemA AND ItemB.

                       

                      But, as said before, this oen of yours was already an outstanding hint in the right direction, thank you so much!

                      I definitely have some material to work upon tomorrow

                • Re: how to create an unique key based on previous values
                  Adam Abwat-Johnson

                  Glad I could help.

                   

                  I'm sure you'll be whizzing through this in no time, it just takes a little practise and knowledge of a few key functions that you may not have used before.

                   

                  Chris

                    • Re: how to create an unique key based on previous values

                      Well, it took me a little more than no time, but I'm getting even closer.

                      Please see attached, if you are still willing to lend a hand.

                       

                       

                      But indeed I am facing a structural problem, as, if this is the table

                       

                      ItemA ItemB Chain
                      AAA BBB Key0
                      AAA HHH Key0
                      AAA QQQ Key0
                      HHH ZZZ Key0
                      LLL MMM Key1
                      MMM BBB Key1...but Key0 too!!
                      NNN OOO Key2
                      QQQ TTT Key1
                      RRR AAA Key0

                       

                      The entry MMM - BBB creates a problem, as it is part of two "chains" and the script cannot go back to the previously entered record, to which a new chain key was assigned, and change the Key from Key1 to Key0.

                       

                      But besides this structural problem (this situation I'm not expecting to happen in real life) some fields still stays empty.

                       

                      Again, if you fancy giving it a last revision, it will be much appreciated!

                      thank you!

                    • Re: how to create an unique key based on previous values
                      Adam Abwat-Johnson

                      Yes, there are many ways to do the same thing in QlikView!

                       

                      What I have done is changed the order of the if statements so that it works i the following way for the current row in the table:

                       

                      1. If the value in ItemA matches a previous ItemA then use the key for that row

                      2. If the value in ItemB matches a previous ItemB then use the key for that row

                      3. If the value in ItemA matches a previous ItemB then use the key for that row

                      4. If the value in ItemB matches a previous ItemA then use the key for that row

                      5. If ItemA and ItemB values are unique then create a new key

                       

                      As for the problem of then assigning row LLL-MMM to Key0 then this gets trickier. I think it may need to then be re-done somehow, but I'm not sure how!

                       

                      I hope i have been of some help anyway, and hopefully you have learnt a few tricks

                       

                      Chris