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
Hi Jagan,
Thanks for your input, i have used an alias in my app. Missed to update here sorry.
In the above script you mentioned it will fetch only the latest data, but i want to group it on month basis. So for each month the latest data should be available. It is working fine from my approach also, but do my approach is storing the complete data ? Because my app size is increasing
Regards
KC
KC,
I think ur script is fine for the latest data , could be possibility size is more because of current latest one.
Why don't you cross check while loading ur qvd and check latest month or date.
This check will gv u clear picture.
Thanks,
AS
Hi KC,
Have you tried what I have suggested?
Hi,
If you do Inner join then the records would be reduced, I think it is not storing complete data. Debug and check the rows count after each load script, this way you can make sure that only required rows are loaded.
Regards,
Jagan.
Hi Rajesh,
How is the whole data being captured using my logic ? I am trying to understand this.
Regards
KC
Hi Balraj,
In this case the group by will not work, since there are other fields also in the table and if you use max(Date) i load statement then also it will create issues.
Thanks for your help
Regards
KC
Hi Khus,
Thanks for your help. In my case i don't want to do any changes at expression level and want to limit the data at script level itself. And i want to understand, do my approach is storing the whole data into the app ?
Regards
KC
tablename:
load *
from table.qvd
inner join
load
max(Date) as Date
resident Tablename
group by monthname(Date);
Instead of doing the Monthname(Date) in group by it is better to create the MonthYear field while you are loading the table.
I think your approach is not loading all the data as you are doing inner join but i think it will also not giving the correct results. You can modify the script like below to limit the data
Tablename:
load *,
monthname(Date) as MonthYear
from table.qvd
inner join
load MonthYear,
max(Date) as Date
resident Tablename
group by MonthYear;
Hi Tamil,
As per your approach it will only load the latest month data.
I want to load the latest date for each month.
Regards
KC
Hi jagan,
Thanks for you input, Do you have any other approach to do the same ? i am bit stuck to identify the reason of sudden increase of size. As per my knowledge also while doing an inner join only the data corresponding to the max date should be loaded into app.
Regards
KC