Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist III
Specialist 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
Highlighted
Creator II
Creator II

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

View solution in original post

9 Replies
Highlighted
Creator II
Creator II

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

View solution in original post

Highlighted
Anonymous
Not applicable

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)

Highlighted
Creator III
Creator III

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

Highlighted
Master
Master

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

Highlighted
Specialist III
Specialist III

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.

Highlighted
Creator III
Creator III

Hi

you can using Week(createdon) to get  week number  

Highlighted

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

Highlighted
MVP
MVP

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

Highlighted
Specialist III
Specialist III

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;