Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Find the latest Year "used"

Hi guys,

I have table with 3 fields Project Number, Year, CAPEX. I need to find in which Year there is a CAPEX in each project. I need to do it within the script.

Here is sample data

   

OptionYear CAPEX
PRJ_0012018521
PRJ_0012019260
PRJ_0012020312
PRJ_00120212605
PRJ_00120221563
PRJ_00120230
PRJ_00120240
PRJ_00120250
PRJ_00120260
PRJ_00120270
PRJ_00120280
PRJ_00120290
PRJ_00120300
PRJ_0022014582
PRJ_00220152330
PRJ_00220162330
PRJ_0022017582
PRJ_00220180
PRJ_00220190
PRJ_00220200
PRJ_0022021582
PRJ_00220220
PRJ_00220230
PRJ_00220240
PRJ_00220250
PRJ_00220260
PRJ_00220270
PRJ_00220280
PRJ_00220290
PRJ_00220300

Thank you

1 Solution

Accepted Solutions

Re: Find the latest Year "used"

Find the logic below

Data:

LOAD * INLINE [

    Option, Year, CAPEX

    PRJ_001, 2018, 521

    PRJ_001, 2019, 260

    PRJ_001, 2020, 312

    PRJ_001, 2021, 2605

    PRJ_001, 2022, 1563

    PRJ_001, 2023, 0

    PRJ_001, 2024, 0

    PRJ_001, 2025, 0

    PRJ_001, 2026, 0

    PRJ_001, 2027, 0

    PRJ_001, 2028, 0

    PRJ_001, 2029, 0

    PRJ_001, 2030, 0

    PRJ_002, 2014, 582

    PRJ_002, 2015, 2330

    PRJ_002, 2016, 2330

    PRJ_002, 2017, 582

    PRJ_002, 2018, 0

    PRJ_002, 2019, 0

    PRJ_002, 2020, 0

    PRJ_002, 2021, 582

    PRJ_002, 2022, 0

    PRJ_002, 2023, 0

    PRJ_002, 2024, 0

    PRJ_002, 2025, 0

    PRJ_002, 2026, 0

    PRJ_002, 2027, 0

    PRJ_002, 2028, 0

    PRJ_002, 2029, 0

    PRJ_002, 2030, 0

]

;

Left Join

Load Option, Max(Year) as LastYear

Resident Data where CAPEX <> 0

group by Option;

Regards,

Kaushik Solanki

7 Replies
saran7de
Contributor III

Re: Find the latest Year "used"

LOAD * INLINE [

    Option, Year, CAPEX

    PRJ_001, 2018, 521

    PRJ_001, 2019, 260

    PRJ_001, 2020, 312

    PRJ_001, 2021, 2605

    PRJ_001, 2022, 1563

    PRJ_001, 2023, 0

    PRJ_001, 2024, 0

    PRJ_001, 2025, 0

    PRJ_001, 2026, 0

    PRJ_001, 2027, 0

    PRJ_001, 2028, 0

    PRJ_001, 2029, 0

    PRJ_001, 2030, 0

    PRJ_002, 2014, 582

    PRJ_002, 2015, 2330

    PRJ_002, 2016, 2330

    PRJ_002, 2017, 582

    PRJ_002, 2018, 0

    PRJ_002, 2019, 0

    PRJ_002, 2020, 0

    PRJ_002, 2021, 582

    PRJ_002, 2022, 0

    PRJ_002, 2023, 0

    PRJ_002, 2024, 0

    PRJ_002, 2025, 0

    PRJ_002, 2026, 0

    PRJ_002, 2027, 0

    PRJ_002, 2028, 0

    PRJ_002, 2029, 0

    PRJ_002, 2030, 0

]

Where CAPEX <> 0

;

Re: Find the latest Year "used"

HI,

You can use the where condition like below.

Load *

from xyz where Capex <> 0;

Regards,

Kaushik Solanki

Not applicable

Re: Find the latest Year "used"

Probably I didn't explain well enough what I need.

I know hot to get reed of the 0 values. I need to know in which year we well make last payment.

something like that:

OptionYearCAPEXFinal Year
PRJ_00120185212022
PRJ_00120192602022
PRJ_00120203122022
PRJ_001202126052022
PRJ_001202215632022
PRJ_001202302022
PRJ_001202402022
PRJ_001202502022
PRJ_001202602022
PRJ_001202702022
PRJ_001202802022
PRJ_001202902022
PRJ_001203002022
PRJ_00220145822021
PRJ_002201523302021
PRJ_002201623302021
PRJ_00220175822021
PRJ_002201802021
PRJ_002201902021
PRJ_002202002021
PRJ_00220215822021
PRJ_002202202021
PRJ_002202302021
PRJ_002202402021
PRJ_002202502021
PRJ_002202602021
PRJ_002202702021
PRJ_002202802021
PRJ_002202902021
PRJ_002203002021

Re: Find the latest Year "used"

Find the logic below

Data:

LOAD * INLINE [

    Option, Year, CAPEX

    PRJ_001, 2018, 521

    PRJ_001, 2019, 260

    PRJ_001, 2020, 312

    PRJ_001, 2021, 2605

    PRJ_001, 2022, 1563

    PRJ_001, 2023, 0

    PRJ_001, 2024, 0

    PRJ_001, 2025, 0

    PRJ_001, 2026, 0

    PRJ_001, 2027, 0

    PRJ_001, 2028, 0

    PRJ_001, 2029, 0

    PRJ_001, 2030, 0

    PRJ_002, 2014, 582

    PRJ_002, 2015, 2330

    PRJ_002, 2016, 2330

    PRJ_002, 2017, 582

    PRJ_002, 2018, 0

    PRJ_002, 2019, 0

    PRJ_002, 2020, 0

    PRJ_002, 2021, 582

    PRJ_002, 2022, 0

    PRJ_002, 2023, 0

    PRJ_002, 2024, 0

    PRJ_002, 2025, 0

    PRJ_002, 2026, 0

    PRJ_002, 2027, 0

    PRJ_002, 2028, 0

    PRJ_002, 2029, 0

    PRJ_002, 2030, 0

]

;

Left Join

Load Option, Max(Year) as LastYear

Resident Data where CAPEX <> 0

group by Option;

Regards,

Kaushik Solanki

Not applicable

Re: Find the latest Year "used"

This just found max value in the year Field (2030) and paste it as the last year to every project...

I tried to play around with where condition, but no success.

saran7de
Contributor III

Re: Find the latest Year "used"

Hi Andrzej, Kaushik's solution is working. Please find the attachment. He added Group By.

If you are looking for something different, please let us know.

Not applicable

Re: Find the latest Year "used"

Thank you, will need to test it more and find why it doesn't wont to work in real life data.