Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
jyothish8807
Master II
Master II

Doubt regarding load

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:

   

DateName Amount
22/1/2016A1000
22/1/2016B322
22/1/2016C234
23/1/2016A999
23/1/2016B322
23/1/2016C212
13/2/2016A123
13/2/2016B4332
13/2/2016C343
14/2/2016A222
14/2/2016B676
14/2/2016C432

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

Best Regards,
KC
24 Replies
Anonymous
Not applicable

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

Kushal_Chawda

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)


Capture.JPG

HirisH_V7
Master
Master

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

HirisH
“Aspire to Inspire before we Expire!”
jagan
Luminary Alumni
Luminary Alumni

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.

Kushal_Chawda

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;

tamilarasu
Champion
Champion

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

Anonymous
Not applicable

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

rajeshforqlikvi
Creator
Creator

Hi by doing this way you are capturing hole data in the resultant physical table including max date.

amit_saini
Master III
Master III

KC,

Try this:

Table:

LOAD

     Max(Date) as Date

FROM ...

GROUP BY MONTHNAME(Date);

Thanks,
AS