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

Hi All,

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)

• ###### Re: Join Load Script issue

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

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

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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!