topic Re: Qlikview script where condition in QlikView

Qlikview script where condition

Mon, 23 Feb 2015 19:39:48 GMT

Hi,

I would like to select last 20 weeks from my base data using where /match condition. I have a field Week_ID, ranges between 1 to 52 for an year. I want select latest 20 weeks. I am trying to write something below. but getting error:

where Count(WEEK_ID)<=52 order by WEEK_ID desc;

Regards, please suggest.

Re: Qlikview script where condition

maxgro — Mon, 23 Feb 2015 19:54:43 GMT

WEEK_ID > 32

Re: Qlikview script where condition

Mon, 23 Feb 2015 20:09:23 GMT

Try this.... WHERE WEEK_ID > 32 ORDER BY WEEK_ID desc;

Re: Qlikview script where condition

sunny_talwar — Mon, 23 Feb 2015 20:15:41 GMT

Just need to use

Where WEEK_ID >= 33;

I am not sure why you need to order WEEK_ID, unless you want to do it for some other reason not mentioned here.

HTH

Best,

Re: Qlikview script where condition

Anonymous — Mon, 23 Feb 2015 20:19:02 GMT

if your WEEK_ID is the number of the week, you don't need count() function and for that matter why do you need to check if it is <= 52?

to select the last 20 weeks, you need something like below:

data1:

Load *

from /*your table*/

Order by WEEK_ID desc;

data2:

noconcatenate First 20 //this selects the latest 20 weeks

load * resident data1;

drop table data1;

Re: Qlikview script where condition

Mon, 23 Feb 2015 20:19:32 GMT

Hi, the issue is not resolved. Let me restructure it.

Suppose this is the data, how can I upload only last 3 weeks data in QVW?

Week	Sales
1	100
2	90
3	80
4	110
5	120
6	90
7	80

Thanks!

Re: Qlikview script where condition

Colin-Albert — Mon, 23 Feb 2015 20:25:53 GMT

First 20 will load the first 20 rows of data.

This is unlikely to be the last 20 weeks of data!

Re: Qlikview script where condition

Mon, 23 Feb 2015 20:27:27 GMT

Good point I need last 3 weeks , not last 3 rows

Re: Qlikview script where condition

Anonymous — Mon, 23 Feb 2015 20:32:22 GMT

Indeed. It will work correctly only if each data row represents exactly one week.

It is safer to have Date in the data (it could be a week start for example), so it can be compared with date(today() - 7 * 20)

Re: Qlikview script where condition

sunny_talwar — Mon, 23 Feb 2015 20:33:35 GMT

Two ways:

Method 1:

Table:

LOAD * INLINE [

Week, Sales

1, 100

2, 90

3, 80

4, 110

5, 120

6, 90

7, 80

];

Table1:

NoConcatenate

LOAD *

Resident Table

Where Week >= 5;

DROP Table Table;

Method 2:

Table:

LOAD * INLINE [

Week, Sales

1, 100

2, 90

3, 80

4, 110

5, 120

6, 90

7, 80

];

Table1:

NoConcatenate

First 3

LOAD *

Resident Table

Order By Week desc;

DROP Table Table;

HTH

Best,

Re: Qlikview script where condition

Colin-Albert — Mon, 23 Feb 2015 20:42:10 GMT

You will also need to consider the start of the year, in week 1 of a new year, the past 3 weeks will be week 1 this year and week 52/51 of last year. (Or week 53/52 last year)

The best way to handle this is to create a field that combines year & week.

For example (WkYear * 100) + WeekNo as YrWeekNo

And then apply Autonumber on this field, loading the data in ascending date order.

This will give a numeric sequence of weeks across all years.

Autonumber(YrWeekNo) as YrWeekSeq

You can then select the last 3 weeks using a set expression on this sequence number

Sum({<YrWeekSeq={“>$(=max(YrWeekSeq -3 ))“}>} Sales)

Re: Qlikview script where condition

maxgro — Mon, 23 Feb 2015 20:43:38 GMT

source:

load *inline [

Week, Sales

1, 100

2, 90

3, 80

4, 110

5, 120

6, 90

7, 80

];

max: load max(Week) as MaxWeek Resident source;

let vMaxWeek=peek('MaxWeek');

final: NoConcatenate load * Resident source where Week >= $(vMaxWeek)-2;

DROP Table source;

Re: Qlikview script where condition

Mon, 23 Feb 2015 20:43:46 GMT

Thanks,

This will not work if there are multiple rows for each week

//Method 2:

Table:
LOAD * INLINE [
Week, Sales
1, 100
2, 90
3, 80
4, 110
5, 120
5,100
6, 90
7, 80
];
Output

5,100

6,90

7,80

Correct Output

5,220

6,90

7,80

Thanks

Re: Qlikview script where condition

johnca — Mon, 23 Feb 2015 20:49:14 GMT

Does it matter what the current week is? I mean, it sounds like you want the latest last 20 weeks not necessarily the last 20 weeks of the (full) year. Is that correct? If so then use something like

Sum({<Week={$(=Max(Week)-3)}>}Sales)

This gets the current year and any previous years' worth of data for those weeks. To get just the current year also add the year filter;

Sum({<Week={$(=Max(Week)-20)}, Year={$(=Max(Year))}>}Sales)

To make it varied add a variable to the mix;

Sum({<Week={$(=Max(Week)-vWeekCount)}, Year={$(=Max(Year))}>}Sales)

and create the variable vWeekCount in an input box or other.

HTH,

John

Re: Qlikview script where condition

sunny_talwar — Mon, 23 Feb 2015 20:58:30 GMT

Yup, that makes sense. I guess some of the folks have a much better way for you to work with. I hope you get the answer you are looking for

Best,

Re: Qlikview script where condition

Mon, 23 Feb 2015 21:26:05 GMT

Hi Massimo, this is a correct answer and indeed a great learning.

Re: Qlikview script where condition

maxgro — Mon, 23 Feb 2015 21:39:02 GMT

first 3 is interesting, maybe this

Table:

LOAD * INLINE [

Week, Sales

8, 1

8, 11

8, 111

1, 100

2, 90

3, 80

4, 110

4, 1100

5, 120

6, 90

7, 80

7, 800

];

Right Keep (Table)

First 3 LOAD Week, sum(1) as dropme

Resident Table

group by Week

Order By Week desc;

DROP field dropme;