How to Function FirstSortedValue or FirstValue

[#1]

Hi EveryOne

I cycle by the phrase the end of the purge

ex)MonthEnd,QuarterEnd,YearEnd

Have a problem to Qlikview to implement.

                    Cycle     MonthEnd     QuarterEnd     YearEnd

20140331    Month             1                    -                    -

20140331    Quarter           -                    1                    -

20140430     Day               1                     -                   -

20140630     Day               1                     1                    -

20140930     Month          1                      1                   -

20141231     Year              1                    1                   1

20151231     Day               1                    1                   1

Trying to make this Data.

In Script

DATA1:
LOAD * INLINE [
    Type, Date, Cycle, Value
    AAAA, 20140131, 1, 1.77
    AAAA, 20140228, 1, 17.12
    AAAA, 20140331, 1, 6.93
    AAAA, 20140331, 2, 18.12
    AAAA, 20140430, 1, 6.86
    AAAA, 20140531, 1, 0.54
    AAAA, 20140630, 1, 10.78
    AAAA, 20140630, 2, 18.04
    AAAA, 20140731, 1, 17.66
    AAAA, 20140831, 1, 18.5
    AAAA, 20140930, 1, 10.18
    AAAA, 20140930, 2, 10.53
    AAAA, 20141031, 1, 7.78
    AAAA, 20141130, 1, 14.99
    AAAA, 20141231, 1, 16.75
    AAAA, 20141231, 2, 9.51
    AAAA, 20141231, 3, 5.25
];
Concatenate(DATA1)
Data2:
LOAD * INLINE [
    Type, Date, Cycle, Value
    BBBB, 20140110, 4, 11.66
    BBBB, 20140222, 4, 0.71
    BBBB, 20140317, 4, 19.02
    BBBB, 20140331, 1, 19.93
    BBBB, 20140430, 1, 1.03
    BBBB, 20140515, 4, 13.52
    BBBB, 20140630, 5, 9.54
    BBBB, 20140630, 2, 16.89
    BBBB, 20140723, 4, 16.86
    BBBB, 20140811, 4, 6.88
    BBBB, 20140912, 4, 5.48
    BBBB, 20140930, 2, 18.37
    BBBB, 20141003, 4, 17.25
    BBBB, 20141130, 1, 13.27
    BBBB, 20141231, 5, 16.35
    BBBB, 20141231, 2, 8.21
    BBBB, 20141231, 3, 1.73
];

Left join (DATA1)
Cycle:
LOAD * INLINE [
    Cycle, Name
    1, MM
    2, QQ
    3, YY
    4, DD
    5, HY
];

Left join (DATA1)
MonthEnd:
Load Type,FirstValue(Cycle) as Cycle,Max(Date) as Date, 1 as MonthEndFlag
Group by Type,YearMonth;
Load Type,
Date,
Left(Date,6) as YearMonth,
Dual(Cycle,Pick(match(Cycle,1,4,2,5,3),1,4,2,5,3)) as Cycle,
Value
Resident DATA1;

Left join (DATA1)
QuarterEnd:
Load Type,FirstValue(Cycle) as Cycle,Max(Date) as Date, 1 as QuarterEndFlag
Group by Type,YearMonth;
Load Type,
Date,
Left(Date,6) as YearMonth,
Dual(Cycle,Pick(match(Cycle,2,1,4,5,3),2,1,4,5,3)) as Cycle,
Value
Resident DATA1
where Match(Mid(Date,5,2),'03','06','09','12')
;

Left join (DATA1)
YearEnd:
Load Type,FirstValue(Cycle) as Cycle,Max(Date) as Date, 1 as YearEndFlag
Group by Type,YearMonth;
Load Type,
Date,
Left(Date,6) as YearMonth,
Dual(Cycle,Pick(match(Cycle,3,2,1,4,5),3,2,1,4,5)) as Cycle,
Value
Resident DATA1
where Mid(Date,5,2)='12';

Use a function FirstValue

Failed to save Flag Data

Help us

!

[#2]