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

[Madina]

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

[rwunderlich]

The fieldname in peek() must be quoted. 

peek('amount')

-Rob

[Ahidhar]

could you share the required output

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

[Madina]

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?  

[Ahidhar]

could you share sample data

[Jeevayswaran]

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

[Madina]

oh, yes, you are right

sorry, I change it

[Madina]

@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 

[Jeevayswaran]

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 · Accepted solution]

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;