Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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?

Tags (3)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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;


talk is cheap, supply exceeds demand
6 Replies
MVP & Luminary
MVP & Luminary

Re: Join Load Script issue

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable

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?

MVP & Luminary
MVP & Luminary

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.


talk is cheap, supply exceeds demand
Not applicable

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

MVP & Luminary
MVP & Luminary

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Join Load Script issue

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