Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Friends,
I am bit confuse and my doubt may sound silly, but need your help to understand this.
I have data coming on daily basis and my requirement is to show the latest data for each month. I have selection on month level, so when ever i select a month the latest data should be available on the app and the previous data should be ignored.
So what i am doing is:
Date | Name | Amount |
22/1/2016 | A | 1000 |
22/1/2016 | B | 322 |
22/1/2016 | C | 234 |
23/1/2016 | A | 999 |
23/1/2016 | B | 322 |
23/1/2016 | C | 212 |
13/2/2016 | A | 123 |
13/2/2016 | B | 4332 |
13/2/2016 | C | 343 |
14/2/2016 | A | 222 |
14/2/2016 | B | 676 |
14/2/2016 | C | 432 |
In script, i am loading the entire data into a QVD, which will have all the data.
Then in application i am doing:
tablename:
load *
from table.qvd
inner join
load
max(Date)
resident Tablename
group by monthname(Date);
This is how i am capturing the latest data for each month.But while doing this, will all the data will be stored in to application or only the latest data will be stored ? I can see a sudden increase in size of application after implementing this logic.
Any suggestion will be really helpful. jagan
Regards
Jyothish KC
I think here you are getting previous data as well.
Storing the whole data in a QVD is fine, I would suggest when you load the data from QVD, then only load the latest data, may be like this?
load *,
max(Date)
From YourQVD
where
Date=Date(max(Date),'YourDateFormat), //Syntax can be corrected
group by monthname(Date);
//this will load only latest data
Hope this will help!!!
you can do something like below
Data:
LOAD *,
Year(DATE) as Year,
Month(DATE) as Month,
MonthName(DATE) as MonthYear;
LOAD *,date(Date#(Date,'DD/MM/YYYY')) as DATE Inline [
Date, Name, Amount
22/1/2016, A, 1000
22/1/2016, B, 322
22/1/2016, C, 234
23/1/2016, A, 999
23/1/2016, B, 322
23/1/2016, C, 212
13/2/2016, A, 123
13/2/2016, B, 4332
13/2/2016, C, 343
14/2/2016, A, 222
14/2/2016, B, 676
14/2/2016, C, 432 ];
Left Join
LOAD MonthYear,
date(max(DATE)) as DATE,
1 as MaxDateFlag
Resident Data
Group by MonthYear;
Now in front end You can select any Year & month
Expression
=sum({<MaxDateFlag={'1'}>}Amount)
Hi,
Like this,
Data:
LOAD * ,
Month(Date) as Month,
Num(Month(Date)) as MonthNum,
Year(Date) as Year
INLINE [
Date, Name, Amount
22/1/2016, A, 1000
22/1/2016, B, 322
22/1/2016, C, 234
23/1/2016, A, 999
23/1/2016, B, 322
23/1/2016, C, 212
13/2/2016, A, 123
13/2/2016, B, 4332
13/2/2016, C, 343
14/2/2016, A, 222
14/2/2016, B, 676
14/2/2016, C, 432
14/2/2017, C, 432
];
In Front end:
Chart Expression-
Sum( {$<[MonthNum] = {"$(=Max([MonthNum]))"},Year={"$(=Max(Year))"}>} Amount )
Hope this Helps,
PFA,
Hirish
Hi,
YOu missed Alias in the Max(Date), may be this is the reason the records are duplicating.
tablename:
load *
from table.qvd
inner join
load
max(Date) AS Date
resident Tablename
group by monthname(Date);
Instead you can also try like this
tablename:
load Date(Max(Date)) AS MaxDate
from table.qvd
LET vMaxDate = Date(Peek('MaxDate', 0));
tablename:
load *
from table.qvd
WHERE Date = '$(vMaxDate )';
This method also loads data faster.
Hope this helps you.
Regards,
Jagan.
if you just want to load the latest Data only in application then do this like below. If you want to load all the data but in front you just want to display latest data in chart then do like in my above post
Data:
LOAD *,
Year(DATE) as Year,
Month(DATE) as Month,
MonthName(DATE) as MonthYear;
LOAD *,date(Date#(Date,'DD/MM/YYYY')) as DATE Inline [
Date, Name, Amount
22/1/2016, A, 1000
22/1/2016, B, 322
22/1/2016, C, 234
23/1/2016, A, 999
23/1/2016, B, 322
23/1/2016, C, 212
13/2/2016, A, 123
13/2/2016, B, 4332
13/2/2016, C, 343
14/2/2016, A, 222
14/2/2016, B, 676
14/2/2016, C, 432 ];
Left Join
LOAD MonthYear,
date(max(DATE)) as DATE,
1 as MaxDateFlag
Resident Data
Group by MonthYear;
Final:
NoConcatenate
LOAD *
Resident Data
where MaxDateFlag='1';
DROP Table Data;
You can also try,
Tablename:
LOAD
Month(Max(Date#(Date,'DD/MM/YYYY'))) as MaxDate
FROM
Test.qvd
(qvd);
LET vMaxDate = Peek('MaxDate');
Drop Table Tablename;
Table:
LOAD *
FROM
Test.qvd
(qvd) where Month(Date#(Date,'DD/MM/YYYY')) = '$(vMaxDate)' ;
Kush141087 agreed with you, this can be done.
By doing in your suggested way, we are loading previous data as well and at UI level we are restricting for our easily calculation.
Correct me , If I am wrong
But, in your case will it not load the previous data included latest data as well??
Hi by doing this way you are capturing hole data in the resultant physical table including max date.
KC,
Try this:
Table:
LOAD
Max(Date) as Date
FROM ...
GROUP BY MONTHNAME(Date);
Thanks,
AS