Last Day in each month in the script

[mario-sarkis]

hey ALL,

I have a question , if i have a table look like this

ID        PeriodDate     Amount

x          1/2/2015         1000

x          1/22/2015       1100

x          1/25/2015       1500

x          2/5/2015         1200

x          2/15/2015       1300

x          2/20/2015       1600

y          1/2/2015         1100

y          1/15/2015       1500

y          1/22/2015       2000

y          2/1/2015         1300

y          2/10/2015       1100

y          2/27/2015       1200

i need to show it like this in the script:

x          1/25/2015       1500

x          2/20/2015       1600

y          1/22/2015       2000

y          2/27/2015       1200

how can i do it in the script?

Thank you in advance,

[PrashantSangle]

Hi,

try like

Load ID,

max(PeriodDate) as NewPeriodDate,

Sum(Amount) as NewAmount

from TableName

Group By ID;

Regards

[Anonymous]

this works when dateformat is defined as 'MM/DD/YYYY'. maybe you donot Need table TEST, then drop it

TEST:
Load * Inline [
ID, PeriodDate, Amount
x, 1/2/2015, 1000
x, 1/22/2015, 1100
x, 1/25/2015, 1500
x, 2/5/2015, 1200
x, 2/15/2015, 1300
x, 2/20/2015, 1600
y, 1/2/2015, 1100
y, 1/15/2015, 1500
y, 1/22/2015, 2000
y, 2/1/2015, 1300
y, 2/10/2015, 1100
y, 2/27/2015, 1200
];


TEST1:
NoConcatenate load
ID,
max(PeriodDate),
max(Amount)
resident TEST
group by ID, month(PeriodDate)

[MK_QSL]

Temp:
Load 
  ID, 
  Date(Date#(PeriodDate,'M/D/YYYY')) as PeriodDate,
  Amount,
  Date(MonthStart(Date#(PeriodDate,'M/D/YYYY')),'YYYYMM') as YearMonth 
Inline
[
  ID,        PeriodDate,     Amount
  x,         1/2/2015,         1000
  x,          1/22/2015,       1100
  x,          1/25/2015,       1500
  x,          2/5/2015,         1200
  x,          2/15/2015,       1300
  x,          2/20/2015,       1600
  y,          1/2/2015,         1100
  y,          1/15/2015,       1500
  y,          1/22/2015,       2000
  y,          2/1/2015,         1300
  y,          2/10/2015,       1100
  y,          2/27/2015,      1200
];


Left Join (Temp)
Load ID, YearMonth, Date(Max(PeriodDate)) as MaxDate Resident Temp
Group By ID, YearMonth;


Final:
Load ID, PeriodDate, Amount Resident Temp 
Where MaxDate = PeriodDate;


Drop Table Temp;

[mario-sarkis]

My PeriodeDate has a number form is this still work ?

or i need to transform it as date ?

[Anonymous]

Qlikview has a dual Interpretation of dateformat: number and date as string. do you mean that? then it should work

if you have an own number you Need to Format it to dateformat

can you post small example qvw?

[tresesco]

Input:
Load
ID,
PeriodDate as Date,
Month(PeriodDate) as Month,
Amount Inline [
ID,PeriodDate,Amount
......
];

Output:
Load
      FirstSortedValue(ID, -Date) as ID,
      Max(Date) as Date,
      FirstSortedValue(Amount, -Date) as Amount
Resident Input Group By ID, Month;

Drop table Input;

PFA

[senpradip007]

Try like

T1:

LOAD *, date(Date#(PeriodDate, 'MM/DD/YYYY')) as Date Inline [

ID,        PeriodDate,     Amount

x ,         1/2/2015,        1000

x ,         1/22/2015,       1100

x ,         1/25/2015,       1500

x ,         2/5/2015,        1200

x ,         2/15/2015,       1300

x ,         2/20/2015,       1600

y ,         1/2/2015,        1100

y ,         1/15/2015,       1500

y ,         1/22/2015,       2000

y ,         2/1/2015,        1300

y ,         2/10/2015,       1100

y ,         2/27/2015,      1200

];

T2:

LOAD

  ID,

  FirstSortedValue(Amount, -Date) as Value,

  Date(Max(Date)) as mDate

Resident T1

Group by ID, month(Date);

[sasiparupudi1]

Data:

load ID,num(Date#(PeriodDate,'M/DD/YYYY')) as PeriodDate,Month(Date#(PeriodDate,'M/DD/YYYY')) as PeriodMonth,

Year(Date#(PeriodDate,'M/DD/YYYY')) as PeriodYear,

Amount Inline [

ID,PeriodDate,Amount

x,  1/2/2015, 1000

x,  1/22/2015,1100

x,  1/25/2015,1500

x,  2/5/2015, 1200

x,  2/15/2015,1300

x,  2/20/2015,1600

y,  1/2/2015, 1100

y,  1/15/2015,1500

y,  1/22/2015,2000

y,  2/1/2015, 1300

y,  2/10/2015,1100

y,  2/27/2015,1200

];

left join

x:

//NoConcatenate

LOAD ID ,

max(PeriodDate) as MaxPeriodDate

Resident Data

group by ID,PeriodYear,PeriodMonth;

Table1:

LOAD ID,Amount,Date(MaxPeriodDate)

Resident Data

where MaxPeriodDate=PeriodDate;

drop table Data;

HTH

Sasi

[tyagishaila]

Hi mario,

script

Dates:

LOAD * INLINE [

    ID, Period, Amount

    x, 1/2/2015, 1000

    x, 1/22/2015, 1100

    x, 1/25/2015, 1500

    x,2/5/2015,1200

  x,2/15/2015,1300

  x,2/20/2015,1600

  y,1/2/2015,1100

  y,1/15/2015,1500

  y,1/22/2015,2000

  y, 2/1/2015,1300

  y,2/10/2015,1100

  y,2/27/2015,1200

];

TAB1:

Load 

    Day(Period) as MaxDay,

    Month(Period) as Month,

    ID as NewID,

    Period as NewPeriod,

    Amount as NewAmount

Resident Dates;

Inner join (TAB1)

TAB2:

Load 

    Max(Day(Period)) as MaxDay,

    Month(Period) as Month,

    ID as NewID   

Resident Dates

group by ID,Month(Period)

;

then show table with fields NewID, NewPeriod and NewAmount