0 Replies Latest reply: Apr 22, 2013 9:29 AM by John De Loach RSS

    Link table on a detail level (by reached value) >> If/where not Exists()

      Hi Qlikviewer,

       

      I'm dealing with following issue, in the last days (and weekend ).

       

      I have two tables they are linked on a general layer (ID). But they have detail information's they have to be linked (SequenceID).

       

      1. Table.A:
      IDA.SequenceIDQuantity

      1000

      1000.1

      100

      2000

      2000.1

      100

      2000

      2000.2

      100

      2000

      2000.3

      100

      2000

      2000.4

      100

      3000

      3000.1

      100

       

       

      1. Table.B:                                                                                

      ID

      B.SequenceID

      B.Quantity

      1000

      1000.A

      50

      1000

      1000.B

      50

      2000

      2000.A

      30

      2000

      2000.B

      70

      2000

      2000.C

      80

      2000

      2000.D

      50

      3000

      3000.A

      100

      …  

       

      >> Result Table

      B.SequenceIDA.SequenceID
      1000.A1000.1
      1000.B1000.1
      2000.A2000.1
      2000.B2000.1
      2000.C2000.2
      2000.D2000.2
      3000.A3000.1

                                                                                                          …

      Drop Field ID

      ...

       

      I have to link them in the way:                                                     

       

      If ID(A)=ID(B) and A.Quantity (for each A.SequenceID) is not reached by B.Quantity then link A.SequenceID to B.SequenceID. (see result table)

       

      I tried to load A and B concatenate with formula but I didn't managed it. I tried to handel it with "for each" A.SequenceID, A.Quantity but then I would need a command like this

       

      If not Exists(Used.BSequenceID,'$(vB.SequenceID)') then ….

       

      and this doesn't work. I assume that the "not Exists()" function just work with a where.

       

      Does somebody have an idea to solve this issue are where I mistaken?

       

      Thanks in advanced.

       

      Best regards

      John D.