topic Re: Doubt regarding load in QlikView

Doubt regarding load

jyothish8807 — Tue, 12 Jan 2016 06:48:52 GMT

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

Re: Doubt regarding load

Anonymous — Tue, 12 Jan 2016 07:00:40 GMT

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

Re: Doubt regarding load

Kushal_Chawda — Tue, 12 Jan 2016 07:05:26 GMT

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)

Re: Doubt regarding load

HirisH_V7 — Tue, 12 Jan 2016 07:07:23 GMT

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

Re: Doubt regarding load

jagan — Tue, 12 Jan 2016 07:08:45 GMT

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.

Re: Doubt regarding load

Kushal_Chawda — Tue, 12 Jan 2016 07:08:59 GMT

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;

Re: Doubt regarding load

tamilarasu — Tue, 12 Jan 2016 07:13:06 GMT

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

Re: Doubt regarding load

Anonymous — Tue, 12 Jan 2016 07:13:44 GMT

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

Re: Doubt regarding load

rajeshforqlikvi — Tue, 12 Jan 2016 07:16:06 GMT

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

Re: Doubt regarding load

amit_saini — Tue, 12 Jan 2016 07:20:01 GMT

KC,

Try this:

Table:

LOAD

Max(Date) as Date

FROM ...

GROUP BY MONTHNAME(Date);

Thanks,
AS

Re: Doubt regarding load

jyothish8807 — Tue, 12 Jan 2016 07:30:01 GMT

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

Re: Doubt regarding load

amit_saini — Tue, 12 Jan 2016 07:33:33 GMT

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,

Re: Doubt regarding load

Kushal_Chawda — Tue, 12 Jan 2016 07:42:13 GMT

Hi KC,

Have you tried what I have suggested?

Re: Doubt regarding load

jagan — Tue, 12 Jan 2016 08:15:17 GMT

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.

Re: Doubt regarding load

jyothish8807 — Tue, 12 Jan 2016 08:55:17 GMT

Hi Rajesh,

How is the whole data being captured using my logic ? I am trying to understand this.

Regards

Re: Doubt regarding load

jyothish8807 — Tue, 12 Jan 2016 09:06:19 GMT

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

Re: Doubt regarding load

jyothish8807 — Tue, 12 Jan 2016 09:08:16 GMT

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

Re: Doubt regarding load

Kushal_Chawda — Tue, 12 Jan 2016 09:28:32 GMT

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;

Re: Doubt regarding load

jyothish8807 — Tue, 12 Jan 2016 09:52:20 GMT

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

Re: Doubt regarding load

jyothish8807 — Tue, 12 Jan 2016 09:55:08 GMT

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