Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
soniasweety
Valued Contributor III

Load Limited data

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

33

32

31

30

........

1 Solution

Accepted Solutions
suryaa30
Contributor II

Re: Load Limited data

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

9 Replies
suryaa30
Contributor II

Re: Load Limited data

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

Partner
Partner

Re: Load Limited data

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)

kenphamvn
Contributor III

Re: Load Limited data

Hi

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

effinty2112
Honored Contributor

Re: Load Limited data

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

soniasweety
Valued Contributor III

Re: Load Limited data

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.

kenphamvn
Contributor III

Re: Load Limited data

Hi

you can using Week(createdon) to get  week number  

ramasaisaksoft
Valued Contributor III

Re: Load Limited data

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

MVP
MVP

Re: Load Limited data

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

soniasweety
Valued Contributor III

Re: Load Limited data

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;