cancel
Showing results for
Did you mean:
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

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

Labels (5)

• ### Script

1 Solution

Accepted Solutions
Creator III

try this

tab:
[
date, costumer

1.12.2020,null

1.1.2021, 10

1.2.2021, 500

1.3.2021, 600
];

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

9 Replies

The fieldname in peek() must be quoted.

peek('amount')

-Rob

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

Contributor III
Author

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?

Creator III

could you share sample data

Contributor III

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

Contributor III
Author

oh, yes, you are right

sorry, I change it

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

Contributor III

Hi, Try this:

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;

Creator III

try this

tab:
[
date, costumer

1.12.2020,null

1.1.2021, 10

1.2.2021, 500

1.3.2021, 600
];

tab1: