Hi,

I have this scenario:

Amount 1 | Type 1 | Amount 2 | Type 2 | Amount 3 |

1515 | R | 1555 | R | 40 |

1555 | R | 1584 | R | 29 |

1584 | R | 43 | E | 43 |

43 | E | 82 | E | 39 |

1584 | R | 77 | R | 77 |

77 | R | 114 | R | 37 |

114 | R | 145 | R | 31 |

I need to get the values for the "Amount 4" Column:

Amount 1 | Type 1 | Amount 2 | Type 2 | Amount 3 | Amount 4 |

1515 | R | 1555 | R | 40 | 0 |

1555 | R | 1584 | R | 29 | 0 |

1584 | R | 43 | E | 43 | 0 |

43 | E | 82 | E | 39 | 0 |

1584 | R | 77 | R | 77 | 82 |

77 | R | 114 | R | 37 | 0 |

114 | R | 145 | R | 3 | 0 |

The calculation for the Amount 4 column is:

If the type of the Amount 2 is E, Amount 4 is 0.

If the types of the Amount 2 and Amount 1 are R:

> If the type of the previous Amount 2 is R, the value of Amount 4 is 0

> If the type of the previous Amount 2 is E, I have to sum all the previous "Amount 3" values with type E until I find one that is R.

For example, in this case** Amount 1 = 1584, Amount 2 = 77**, both values have a R-Type. The previous Amount 2 value (82) is E-Type. So, I need to sum the Amount 3 values 39 and 43, the result is 82. Value 29 must not be added because it belongs to an Amount 2 value (1584) with a R-Type.

How can I get the Amount 4 values with this logic in Qlikview? What functions are useful to solve this case? I am new to Qlikview and I am trying to find a solution.

Thanks in advance.

Best Regards,

Lucía

This content has been marked as final.
Show 0 replies