Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
........
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);
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);
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)
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);
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
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.
Hi
you can using Week(createdon) to get week number
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
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)'
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;