6 Replies Latest reply: Apr 7, 2014 3:55 AM by Lorenzo Biancofiore RSS

    Join Load Script issue

      Hi All,

       

       

      i need your help to solve a Loading Script issue.

      I Have three table in my cloud:

       

       

      table a --> day field and Amount_A

      table b --> day field and Amount_B

      final table --> day field and Amount (Amount filled only for the last day of the month)

       

       

      i need to join the final table with the a and b tables in order to calculate the amount of the final table for the days different from the last day of the month.

      table a

      daycityAmount_A
      31-Jan-14110
      31-Jan-14211
      1-Feb-14120
      1-Feb-14243
      2-Feb-1417
      2-Feb-1424
      3-Feb-1414
      3-Feb-1425
      28-feb-1412
      28-feb-1423
      01-mar-1413
      01-mar-1423

      table b

      daycityAmount_B
      31-Jan-14115
      31-Jan-1426
      1-Feb-14125
      1-Feb-1426
      2-Feb-14110
      2-Feb-1424
      3-Feb-1412
      3-Feb-1428
      28-feb-1413
      28-feb-1423
      01-mar-1412
      01-mar-1422

      (starting) Result table

      daycityAmount
      31-Jan-141100
      31-Jan-14234
      1-Feb-141
      1-Feb-142
      2-Feb-141
      2-Feb-142
      3-Feb-141
      3-Feb-142
      28-feb-14175
      28-feb-14245
      01-mar-141
      01-mar-142

      (final) Result table

      daycityAmount
      31-Jan-141100
      31-Jan-14234
      1-Feb-141=Amount_city1(31/Jan)-Amount_A_city1(1/Feb)+Amount_B_city1(1/Feb)
      1-Feb-142=Amount_city2(31/Jan)-Amount_A_city2(1/Feb)+Amount_B_city2(1/Feb)
      2-Feb-141=Amount_city1(1/Feb)-Amount_A_city1(2/Feb)+Amount_B_city1(2/Feb)
      2-Feb-142=Amount_city2(1/Feb)-Amount_A_city2(2/Feb)+Amount_B_city2(2/Feb)
      3-Feb-141=Amount_city1(2/Feb)-Amount_A_city1(3/Feb)+Amount_B_city1(3/Feb)
      3-Feb-142=Amount_city2(2/Feb)-Amount_A_city2(3/Feb)+Amount_B_city2(3/Feb)
      28-feb-14175
      28-feb-14245
      01-mar-141=Amount_city1(28/Feb)-Amount_A_city1(1/Mar)+Amount_B_city1(1/Mar)
      01-mar-142=Amount_city2(28/Feb)-Amount_A_city2(1/Mar)+Amount_B_city2(1/Mar)

      Can you please help me?

        • Re: Join Load Script issue
          Gysbert Wassenaar

          See attached qvw.

            • Re: Join Load Script issue

              Hi and thanks for the reply, but it is partially wrong.

              I need to calculate the amount the result of

              date 2/2 city 2 must be 83 + 4 + 4 = 91 not 8.

              i must consider the Amount field of date 1/2.

              Can you help me?

                • Re: Join Load Script issue
                  Gysbert Wassenaar

                  Obviously I don't understand your logic.

                   

                  day          city     value:

                  2-Feb-14      2      =Amount_city2(1/Feb)-Amount_A_city2(2/Feb)+Amount_B_city2(2/Feb)

                   

                  Amount_city2(1/Feb) = null

                  Amount_A_city2(2/Feb) = 4

                  Amount_B_city2(2/Feb) = 4

                   

                  null - 4 + 4 = 0.

                   

                  Can you explain where the 83 comes from? It's not in the data you posted. And can you make clear if you want to subtract Amount_A_city2 or add it. Your expression tells me to subtract, but in your post above you add.

                    • Re: Join Load Script issue

                      i'm sorry.. the error is on Amount_city2(1/Feb) and on the sign (it is less important, assume to use +)

                       

                      day          city     value:

                      1-Feb-14      2      = Amount_city2(31/Jan) + Amount_A_city2(1/Feb) + Amount_B_city2(1/Feb) = 83

                      Amount_city2(31/Jan) = 34

                      Amount_A_city2(1/Feb) = 43

                      Amount_B_city2(1/Feb) = 6


                      for the 2-Feb it is


                      Amount_city2(1/Feb) = 83

                      Amount_A_city2(2/Feb) = 4

                      Amount_B_city2(2/Feb) = 4


                      I hope now it is clear

                        • Re: Join Load Script issue
                          Gysbert Wassenaar

                          I hope so too. Try replacing the last load statement with:

                           

                          T2:
                          load day, city, if(previous(city)=city and len(trim(Amount))=0, rangesum(peek(Amount),Amount_A,Amount_B), Amount) as Amount
                          Resident T1
                          order by city, day;
                          
                            • Re: Join Load Script issue

                              Great!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!