How to Function FirstSortedValue or FirstValue

Fri, 24 Oct 2014 08:45:12 GMT

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

Re: How to Function FirstSortedValue or FirstValue

Fri, 24 Oct 2014 08:47:13 GMT

topic Re: How to Function FirstSortedValue or FirstValue in QlikView

How to Function FirstSortedValue or FirstValue

Re: How to Function FirstSortedValue or FirstValue