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: 
valpassos
Creator III
Creator III

Stock calculation with nested IFs

Hi community,

I have the following piece of code:

IF(ID_Company=Previous(ID_Company),
   IF(Reference=Previous(Reference),
      IF(Warehouse=Previous(Warehouse),
           Peek("Final Stock")-Peek("Stock Flows"),
           "Final Stock"),
      "Final Stock"),
   "Final Stock")                                                                                                  AS "Final Stock",

IF(ID_Company=Previous(ID_Company),
   IF(Reference=Previous(Reference),
      IF(Warehouse=Previous(Warehouse),
          Peek("Final Stock")-Peek("Stock Flows"),
          "Final Stock"),
     "Final Stock"),
  "Final Stock") - "Stock Flows"                                                                    AS "Initial Stock"

Could someone please explain to me, in verbose mode, what this piece of script is doing?
I know it's calculating the Initial Stock based on Final Stock, but I don't entirely understand the if-else logic. There is way too else conditions.
For instance: there is a else condition "Final Stock" for each IF, but what is this doing, functionally?

Thanks in advance!

Labels (2)
2 Solutions

Accepted Solutions
JordyWegman
Partner - Master
Partner - Master

HI Valpassos,

What it is doing is as follows:

IF(ID_Company=Previous(ID_Company),
// it will check, on the row, if the company name on that row is the same as the company name on the previous row. Say, on row 10 you have company XYZ, it will check if on row 9, the company is also called XYZ. If not, it will return the Final Stock.

   IF(Reference=Previous(Reference),
For this part the same, as it will check the reference.

      IF(Warehouse=Previous(Warehouse),
Then also for this part where it will check the warehouse name.

           Peek("Final Stock")-Peek("Stock Flows"),
Now it will check with peek for the result of this function, called Final Stock. It will now subtract the Stock Flows.

           "Final Stock"),
If the warehouse name is not the same, it will return the Final Stock.

      "Final Stock"),
If the reference name is not the same, it will return the Final Stock.

   "Final Stock")
If the ID_Company name is not the same, it will return the Final Stock.
                                                                                                  AS "Final Stock"

 

Jordy

Climber

Work smarter, not harder

View solution in original post

JordyWegman
Partner - Master
Partner - Master

Hi Lisa,

This would result in the same answer. So you can use both if you want. And yes, all happen in that order, but also for your  'AND' function. 

Jordy

Climber

Work smarter, not harder

View solution in original post

5 Replies
JordyWegman
Partner - Master
Partner - Master

HI Valpassos,

What it is doing is as follows:

IF(ID_Company=Previous(ID_Company),
// it will check, on the row, if the company name on that row is the same as the company name on the previous row. Say, on row 10 you have company XYZ, it will check if on row 9, the company is also called XYZ. If not, it will return the Final Stock.

   IF(Reference=Previous(Reference),
For this part the same, as it will check the reference.

      IF(Warehouse=Previous(Warehouse),
Then also for this part where it will check the warehouse name.

           Peek("Final Stock")-Peek("Stock Flows"),
Now it will check with peek for the result of this function, called Final Stock. It will now subtract the Stock Flows.

           "Final Stock"),
If the warehouse name is not the same, it will return the Final Stock.

      "Final Stock"),
If the reference name is not the same, it will return the Final Stock.

   "Final Stock")
If the ID_Company name is not the same, it will return the Final Stock.
                                                                                                  AS "Final Stock"

 

Jordy

Climber

Work smarter, not harder
valpassos
Creator III
Creator III
Author

Exactly I was looking for!

Thanks, @JordyWegman!

valpassos
Creator III
Creator III
Author

[REOPENED]

Hi!

I have still another question to make regarding this issue.

We have this structure:

IF(ID_Company=Previous(ID_Company),
    IF(Reference=Previous(Reference),
        IF(Warehouse=Previous(Warehouse),
         Peek("Final Stock")-Peek("Stock Flows"),
      "Final Stock"),
   "Final Stock"),
"Final Stock")                                                                        AS "Final Stock"


How different is this IF chain from doing:
IF(ID_Company=Previous(ID_Company)
     AND Reference=Previous(Reference)
     AND Warehouse=Previous(Warehouse),... ?

When we do an IF for each condition, do all the 3 IFs need to happen (intersection) in order to do the peek()?

Thanks once again community,

Lisa

JordyWegman
Partner - Master
Partner - Master

Hi Lisa,

This would result in the same answer. So you can use both if you want. And yes, all happen in that order, but also for your  'AND' function. 

Jordy

Climber

Work smarter, not harder
valpassos
Creator III
Creator III
Author

Alright!

Many thanks for the prompt reply, @JordyWegman  🙂

Lisa