topic Re: Load Limited data in QlikView

Load Limited data

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a data last 30 weeks data ,

but Its one table in database ,so I want to load only last two weeks data into qlik

how can I restrict the data? I have week number as column in db

weekNumber

........

Re: Load Limited data

suryaa30 — Tue, 05 Sep 2017 07:54:59 GMT

Do you have any time stamp or date field along with weekNumber?

How is the weekNumber related to date or timestamp?

LOAD *,

Table from DB;

Temp:

LOAD

max(weekNumber,3) as ThirdMaxNum

Resident Table;

Let vThirdMaxNum= Num(Peek('ThirdMaxNum',0,'Temp'));

DROP TABLE Temp;

LOAD *,

Resident Table

where num(weekNumber)>$(vThirdMaxNum);

Re: Load Limited data

Anonymous — Tue, 05 Sep 2017 07:59:30 GMT

if your data contains data upto current week

LOAD *,

where (weekNumber>week(today()-2);

if your data contains data upto previous week

LOAD *,

where (weekNumber>week(today()-3);

otherwise you need to get the highest weeknumber of your data (eg. by grouping and max weeknumber)

Re: Load Limited data

kenphamvn — Tue, 05 Sep 2017 08:04:37 GMT

First, you need getlast two weekNumber from data table

NoConcatenate
[Last2Week]:
First 2 load WeekNum as WeekList
Resident DataTable
Order by WeekNum desc;

Second, get data with 2 last week filter

NoConcatenate
[Finaltable]
load * Resident DataTable
where Exists(Weeklist,WeekNum);

Re: Load Limited data

effinty2112 — Tue, 05 Sep 2017 08:58:14 GMT

Hi Surya,

I think the solution you gave Sony is a good one. Can I make a small suggestion that will be quicker when handling large datasets that you might find interesting?

Temp:

LOAD max(Temp,3) as ThirdMaxNum;

LOAD FieldValue('weekNumber',RecNo()) as Temp

AutoGenerate FieldValueCount('weekNumber');

The difference here is that instead of going though the table Table looking for the third biggest value of weekNumber we only look though the distinct field values for weekNumber.

Kind regards

Andrew

Re: Load Limited data

soniasweety — Tue, 05 Sep 2017 09:29:30 GMT

thanks all for quick response.

if I don't have the weeknumber column how can I get the last week and current week?

I have createdon as date field.

Re: Load Limited data

kenphamvn — Tue, 05 Sep 2017 09:55:58 GMT

you can using Week(createdon) to get week number

Re: Load Limited data

ramasaisaksoft — Tue, 05 Sep 2017 11:21:35 GMT

Table:

LOAD *,

InWeekToDate(Date, WeekEnd(Today()), 0) as CurrentWeek,

InWeekToDate(Date, WeekEnd(Today()), -1) as PreviousWeek,

InMonthToDate(Date, MonthEnd(Today()), 0) as CurrentMonth;

LOAD Date(MonthStart(Today(), -1) + RecNo() - 1) as Date

AutoGenerate 365;

so use this type of code to limit the data

Re: Load Limited data

maxgro — Tue, 05 Sep 2017 11:57:15 GMT

maybe you can set a variable, (replace the ??????, it depends on the database you se)

LET vDateFrom=Date(WeekStart(Today())-7, '??????');

and use the variable in the SQL

...

SQL SELECT field1, ...

FROM table

WHERE createdon >= '$(vDateFrom)'

Re: Load Limited data

soniasweety — Wed, 06 Sep 2017 05:20:14 GMT

Hi,

I have Qvd gen and Main application,

where can I write this code?

qvd_Gen:

load id,

WeekNumber,

name

.....;

sql select

id,

weeknumber,

name

....from dbo.sample;