topic Re: Last Day in each month in the script in QlikView

Last Day in each month in the script

mario-sarkis — Thu, 30 Jul 2015 06:32:57 GMT

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,

Re: Last Day in each month in the script

PrashantSangle — Thu, 30 Jul 2015 06:42:21 GMT

Hi,

try like

Load ID,

max(PeriodDate) as NewPeriodDate,

Sum(Amount) as NewAmount

from TableName

Group By ID;

Regards

Re: Last Day in each month in the script

Anonymous — Thu, 30 Jul 2015 06:42:49 GMT

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)

Re: Last Day in each month in the script

MK_QSL — Thu, 30 Jul 2015 06:44:38 GMT

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;

Re: Last Day in each month in the script

mario-sarkis — Thu, 30 Jul 2015 06:46:09 GMT

My PeriodeDate has a number form is this still work ?

or i need to transform it as date ?

Re: Last Day in each month in the script

Anonymous — Thu, 30 Jul 2015 06:52:26 GMT

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?

Re: Last Day in each month in the script

tresB — Thu, 30 Jul 2015 06:57:56 GMT

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

Re: Last Day in each month in the script

senpradip007 — Thu, 30 Jul 2015 07:07:42 GMT

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);

Re: Last Day in each month in the script

sasiparupudi1 — Thu, 30 Jul 2015 08:39:26 GMT

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

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

Re: Last Day in each month in the script

tyagishaila — Thu, 30 Jul 2015 11:02:33 GMT

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

Re: Last Day in each month in the script

tyagishaila — Thu, 30 Jul 2015 11:17:41 GMT

Dates:

LOAD Day(Period) as MaxDay,

Month(Period) as Month,

Period,

ID,

Amount

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

];

Inner join

TAB2:

Load

Max(Day(Period)) as MaxDay,

Month(Period) as Month,

Resident Dates

group by ID,Month(Period)

;

Re: Last Day in each month in the script

mario-sarkis — Thu, 30 Jul 2015 11:21:04 GMT

hi Pardib this my sampl Data i used your Expression it works but my request is i need to show a table that calculate the amount in max date VS the amount in the Previous date right before it here is my example:

ID	Balance	Date
07019	20994.09	3/19/2015
07019	21035.58	11/26/2014
07019	21077.07	12/31/2013
07019	20007	1/31/2014
07019	17000	2/28/2014
07019	18000	4/30/2014
07019	12000	5/31/2014
07019	10000	6/30/2014
07019	14000	7/31/2014
07019	25000	8/31/2014
07019	55000	9/25/2014
07019	153000	10/17/2014
00534	144551	3/19/2015
00534	153420	11/26/2014
00534	161708	12/31/2013
00534	25000	1/31/2014
00534	26000	2/28/2014
00534	29000	4/30/2014
00534	35000	5/31/2014
00534	45000	6/30/2014
00534	26000	7/31/2014
00534	29000	8/31/2014
00534	22000	9/25/2014
00534	21000	10/17/2014

in this Case i need my output will show if no selection on any field the Amount on max date VS the previous one in my example :

ID MaxDate (3/19/2015) PreviousDate(11/26/2014) Lost Amount

07019 20994.09 21035.58 20994.09-21035.58=(41.49)

00534 144551 153420 153420-144551=8869

if i select on for example 10/17/2014 i get

ID MaxDate (10/17/2014) PreviousDate(9/25/2014) Lost Amount

07019 153000 55000 153000 - 55000=98000

00534 21000 22000 21000-22000 =(1000)

Hope you can help thanks

Re: Last Day in each month in the script

ankitbisht01 — Fri, 31 Jul 2015 10:56:56 GMT

Here is one more simple script and you can also find attached solution qvw.

load ID,

PeriodDate,

month(PeriodDate) as month,

Amount

;

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

];

Inner Join(A) /* we are doing inner join and not droping the table A and loading all fileds in table B. because table A is having 'amount' field which will not work with Group by function without using sum(amount) in this case it will not provide desired result as it will sum up all the values.

Load ID,

month,

Date(Max(PeriodDate)) as PeriodDate

Resident A Group By ID, month;

Regards

Ankit Bisht