Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

1 Solution

Accepted Solutions
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;


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

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;


talk is cheap, supply exceeds demand
Not applicable
Author

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