3 Replies Latest reply: Jan 22, 2012 4:22 AM by Jean-Pierre Bakhache RSS

    Expression link with 2 colums from 2 tables

      Hi,

       

      I Have 2 tables linked with one column

       

      Invoice

           fam_num

           residence_ num_invoice

       

      Residence

           fam_num

           residence_num

       

      I am trying to make a chart where the residence_num_invoice = residence num

       

      The expression I am using is given below:

       

      sum({<[RESIDENCE_NUM_INVOICE]=[RESIDENCE_NUM]>}ENTREE)

       

      But I get Result = 0

       

      When I am replace RESIDENCE_NUM by a number that exists, all is right

      sum({<[RESIDENCE_NUM_INVOICE]={4117}>}ENTREE) for example

       

      What's the problem in the first expression ?

       

      Any help would be greatly appreciated.

        • Expression link with 2 colums from 2 tables
          Jean-Pierre Bakhache

          hi,

           

          maybe something like this might help:

           

          Sum(If(RESIDENCE_NUM_INVOICE = RESIDENCE_NUM, ENTREE, 0))

            • Expression link with 2 colums from 2 tables

              Thanks

               

              That's work but I don't understand why it dosn't work with the the expression

              sum({<[RESIDENCE_NUM_INVOICE]=[RESIDENCE_NUM]>}ENTREE)

                • Expression link with 2 colums from 2 tables
                  Jean-Pierre Bakhache
                  This is because the expression assumes that a selection was made in the field RESIDENCE_NUM so you can compare it to RESIDENCE_NUM_INVOICE (otherwise it returns Null).
                  That's why it worked when you passed a real value {4117}, and will also work if you select that value from a listbox.
                  Your expression can work when using the Possible values function P() like this:
                  I tried with a very simple scenario:

                  t1:
                  LOAD * Inline [
                  fam_num, residence_num_invoice, entree
                  1,1123,55
                  1,2233,40
                  2,1132,45
                  2,3344,122
                  3,2535,500
                  ]
                  ;

                  t2:
                  LOAD * Inline [
                  fam_num, residence_num
                  1,1111
                  1,1123
                  2,1132
                  2,4433
                  ]
                  ;
                  and using this as expression in a pivot table having fam_num as dimension:

                   

                  sum({$<residence_num_invoice = P({$} residence_num)>} entree)

                  and i got same result as the expression with the if condition.
                  you might have to switch between both of above fields depending on your case, just try to see which one gives you your desired result.
                  Hope i was able to explain at least what i know .