Doubt regarding load

[jyothish8807]

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

[NareshGuntur]

Hi Jyothish,

Please try this.

The below is the output I got.

DateFinal	NameFinal	AmountFinal
23/1/2016	C	212
22/1/2016	C	234
13/2/2016	C	343
14/2/2016	C	432

Cheers,

Naresh

[jyothish8807]

Hi Naresh,

Thanks for your input, but the output i require is different.

This is the output i require, which i am able to achieve. I want to understand , if all the data is getting loaded in to the app or just the latest data is being loaded using my approach.

Date	Name	Amount
23/1/2016	A	999
23/1/2016	B	322
23/1/2016	C	212
14/2/2016	A	222
14/2/2016	B	676
14/2/2016	C	432

Regards

KC

[rajeshforqlikvi]

HI KC,

after the join condition its going to be a one physical table and contains a kind of union type of records in the table.

our approach should be in such a manner that our data should not grow for this we can reduce the data from bottom year as we know that it will grow from current year .I mean to say that for reporting purpose might some time 1 year , 6  months data will be sufficient for the report as per the data frequency. so here we will keen only 1 year or 6 months of data for reporting so that data will not grow rapidly and not a worry for data storage (Memory).

[jagan]

Hi Jyothish,

Try like this

Data:

LOAD

Date(Date#(Date, 'D/M/YYYY')) AS Date,

Name,

Amount,

MonthName(Date(Date#(Date, 'D/M/YYYY'))) AS Month

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

INNER JOIN(Data)

LOAD

Month,

Max(Date) AS Date

RESIDENT Data

Group By Month;

Regards,

Jagan.

[jyothish8807]

Hi Saini,

Thanks for your input, but i have more fields in my table so wont be able to do a group by like this.

Regards

KC