Join Load Script issue

[#1]

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
day	city	Amount_A
31-Jan-14	1	10
31-Jan-14	2	11
1-Feb-14	1	20
1-Feb-14	2	43
2-Feb-14	1	7
2-Feb-14	2	4
3-Feb-14	1	4
3-Feb-14	2	5
…		…
28-feb-14	1	2
28-feb-14	2	3
01-mar-14	1	3
01-mar-14	2	3

table b
day	city	Amount_B
31-Jan-14	1	15
31-Jan-14	2	6
1-Feb-14	1	25
1-Feb-14	2	6
2-Feb-14	1	10
2-Feb-14	2	4
3-Feb-14	1	2
3-Feb-14	2	8
…		…
28-feb-14	1	3
28-feb-14	2	3
01-mar-14	1	2
01-mar-14	2	2

(starting) Result table
day	city	Amount
31-Jan-14	1	100
31-Jan-14	2	34
1-Feb-14	1
1-Feb-14	2
2-Feb-14	1
2-Feb-14	2
3-Feb-14	1
3-Feb-14	2
…		…
28-feb-14	1	75
28-feb-14	2	45
01-mar-14	1
01-mar-14	2

(final) Result table
day	city	Amount
31-Jan-14	1	100
31-Jan-14	2	34
1-Feb-14	1	=Amount_city1(31/Jan)-Amount_A_city1(1/Feb)+Amount_B_city1(1/Feb)
1-Feb-14	2	=Amount_city2(31/Jan)-Amount_A_city2(1/Feb)+Amount_B_city2(1/Feb)
2-Feb-14	1	=Amount_city1(1/Feb)-Amount_A_city1(2/Feb)+Amount_B_city1(2/Feb)
2-Feb-14	2	=Amount_city2(1/Feb)-Amount_A_city2(2/Feb)+Amount_B_city2(2/Feb)
3-Feb-14	1	=Amount_city1(2/Feb)-Amount_A_city1(3/Feb)+Amount_B_city1(3/Feb)
3-Feb-14	2	=Amount_city2(2/Feb)-Amount_A_city2(3/Feb)+Amount_B_city2(3/Feb)
…		…
28-feb-14	1	75
28-feb-14	2	45
01-mar-14	1	=Amount_city1(28/Feb)-Amount_A_city1(1/Mar)+Amount_B_city1(1/Mar)
01-mar-14	2	=Amount_city2(28/Feb)-Amount_A_city2(1/Mar)+Amount_B_city2(1/Mar)

Can you please help me?

[Gysbert_Wassenaar]

See attached qvw.

[#3]

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?

[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.

[#5]

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

[Gysbert_Wassenaar · Accepted solution]

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;

[#7]

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