Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Madina
Contributor III
Contributor III

Cant take newly counted previous row into current row in load script

Hello everyone,

I have a table costumer with fields date, amount

 

 

date, costumer   //Wrote here incorrect as date, customer, has to be date, amount
date, amount  // right version

1.1.2021,   500

1.2.2021, 600

etc

 

Next what I do, is load new table, costumer2

load
date,

if (isnull(amount ) and isnull(previous(amount)),    '',

                                                                           if (isnull(previous(amount), 10, peek(amount)*12)) as amount

Resident costumer;

The thing is that it does not count , it lefts as null. 

If I write precious instead of peek, for first date it takes 500, but others it takes amount as not not calculated, but as it is in table customer. I mean for 1 january, it saves 10, but for 1 february it takes previous(amount) as 500 and multiplies it by 12.

How to explain to qlik to take 10, instead of 500?

 

Hope, explained it well

Regards, Madina
Labels (5)
1 Solution

Accepted Solutions
Ahidhar
Creator III
Creator III

try this

tab:
load date#(date,'D.M.YYYY') as date,costumer;
load * Inline
[
date, costumer

1.12.2020,null

1.1.2021, 10

1.2.2021, 500

1.3.2021, 600
];

tab1:
load
date,
if(Previous(costumer)='null',10,peek(amount)*(costumer-100)) as amount
resident tab; drop table tab;

View solution in original post

9 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The fieldname in peek() must be quoted. 

peek('amount')

-Rob

Ahidhar
Creator III
Creator III

could you share the required output

if (isnull(costumer) and isnull(previous(costumer)), '',
if (isnull(peek(amount)), 10, peek(amount)*12)) as amount

Madina
Contributor III
Contributor III
Author

hello, thank you for your reply. @Ahidhar , @rwunderlich 

I have some updates for this problem. Let me explain from beginning.

what initially I wanted is to have table like that . If for example, I had dates started from november 2020 and no records for that dates, I will get null for that.  And only when I see first record on january 2021, I make it 10. and for next ones, just multiply previous record by 12.  Will be look like this

date, amount

1.11.2020, null

1.12.2020, null

1.1.2021,   10

1.2.2021, 10*12

1.3.2021, (10*12)*12,

etc

@rwunderlich  suggestion for this case was right.   I need to write 

if (isnull(amount ) and isnull(previous(amount)),    '',

                                                                           if (isnull(previous(amount)), 10, peek('amount')*12)) as amount

Now, I met another problem, if I modify my formula. If I want to multiply not by 12, but by (current amount-100), It does not work. 

Again, what I want,

date, amount

1.12.2020, null

1.1.2021,   10

1.2.2021, 10*(500-100)

1.3.2021, 10*(500-100)*(600-100),

etc

 

How to do that?  

Regards, Madina
Ahidhar
Creator III
Creator III

could you share sample data

Jeevayswaran
Contributor III
Contributor III

Hi, is the last line value calculation correct? is not 1.3.2021, 10*(500-100)*(600-100)??

Madina
Contributor III
Contributor III
Author

oh, yes, you are right

sorry, I change it

Regards, Madina
Madina
Contributor III
Contributor III
Author

@Ahidhar , sorry, I cant, because its in remote desctop. But here I changed last row in explanation to

 1.3.2021, 10*(500-100)*(600-100),

 

So, hope, it is now clearer 

Regards, Madina
Jeevayswaran
Contributor III
Contributor III

Hi, Try this: 

LOAD
date,
amount,
if(IsNull(Previous(amount)),'null',
if(Peek(Amount_New)='null',10,Peek(Amount_New)*(amount-100))) AS Amount_New
Resident tablename order by date asc;

Ahidhar
Creator III
Creator III

try this

tab:
load date#(date,'D.M.YYYY') as date,costumer;
load * Inline
[
date, costumer

1.12.2020,null

1.1.2021, 10

1.2.2021, 500

1.3.2021, 600
];

tab1:
load
date,
if(Previous(costumer)='null',10,peek(amount)*(costumer-100)) as amount
resident tab; drop table tab;