Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
soniasweety
Master
Master

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

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)

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

effinty2112
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

soniasweety
Master
Master
Author

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
Creator III
Creator III

Hi

you can using Week(createdon) to get  week number  

ramasaisaksoft

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

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

soniasweety
Master
Master
Author

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;