Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mario-sarkis
Creator II
Creator II

Last Day in each month in the script

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,

12 Replies
PrashantSangle

Hi,

try like

Load ID,

max(PeriodDate) as NewPeriodDate,

Sum(Amount) as NewAmount

from TableName

Group By ID;

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Anonymous
Not applicable

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
MVP
MVP

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
Creator II
Creator II
Author

My PeriodeDate has a number form is this still work ?

or i need to transform it as date ?

Anonymous
Not applicable

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
MVP
MVP

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
Specialist III
Specialist III

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
Master III
Master III

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
Specialist
Specialist

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