topic Re: Filter out data in QlikView

Filter out data

belromvar — Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

I wonder how can I filter out entries from the loaded data. From my data I want to discriminate all users that have count of distinct dates > 2. So In this case I want to drop all information from U1 and in *all* the reports work only with U2 and U3

My data looks like this:

User Area Date

U1 A1 2017-08-11

U1 A2 2017-08-12

U1 A1 2017-08-13

U1 A1 2017-08-14

U2 A1 2017-08-12

U3 A5 2017-08-12

Thanks in advance for the help.

Re: Filter out data

Anil_Babu_Samineni — Fri, 01 Sep 2017 09:47:27 GMT

May be this?

Load * From Table Where Not Match(User,'U1');

Re: Filter out data

Anil_Babu_Samineni — Fri, 01 Sep 2017 09:56:50 GMT

Or Front End may be this?

Count({<User = E({<User = {'U1'}>}User), Date = {"=Count(DISTINCT Date)>2"}>} Distinct Date)

Re: Filter out data

effinty2112 — Fri, 01 Sep 2017 10:00:29 GMT

Hi Belia,

try:

Data:

Load * Inline [

User, Area, Date

U1, A1, 2017-08-11

U1, A2, 2017-08-12

U1, A1, 2017-08-13

U1, A1, 2017-08-14

U2, A1, 2017-08-12

U3, A5, 2017-08-12

];

Inner Join(Data)

LOAD

User

Where CntDate <=2;

LOAD

User,

count(DISTINCT Date) as CntDate

Resident Data Group by User;

giving:

User	Area	Date
U2	A1	2017-08-12
U3	A5	2017-08-12

Cheers

Andrew

Re: Filter out data

antoniotiman — Fri, 01 Sep 2017 10:00:46 GMT

Hi Belia,

in Script

Temp:
LOAD * Inline [
User Area Date
U1 A1 2017-08-11
U1 A2 2017-08-12
U1 A1 2017-08-13
U1 A1 2017-08-14
U2 A1 2017-08-12
U3 A5 2017-08-12
](delimiter is spaces);
Inner Join LOAD User Where Count = 1;
LOAD User,Count(User) as Count
Resident Temp
Group By User;

Regards,

Antonio

Re: Filter out data

effinty2112 — Fri, 01 Sep 2017 10:10:07 GMT

Hi Belia,

Maybe use a calculated dimension:

=Aggr(if(count(DISTINCT Date) <=2,User),User)

labelled 'User' in this straight table.

User	Concat(DISTINCT Area)
U2	A1
U3	A5

Cheers

Andrew

Re: Filter out data

Kushal_Chawda — Fri, 01 Sep 2017 11:59:37 GMT

Script Solution

Data:

Load * Inline [

User, Area, Date

U1, A1, 2017-08-11

U1, A2, 2017-08-12

U1, A1, 2017-08-13

U1, A1, 2017-08-14

U2, A1, 2017-08-12

U3, A5, 2017-08-12

];

left join(Data)

LOAD User,

count(distinct Date) as CountOfDates

resident Data

group by User;

Create chart

Dimension:

User

Area

Expression:

= only({<CountOfDates={"<=2"}>} Date)

Front end solution

Create chart

Dimension:

User

Area

Expression:

= only({<User={"=count(distinct Date)<=2"}>} Date)

Re: Filter out data

belromvar — Fri, 01 Sep 2017 12:08:31 GMT

That's exactly what I was looking for. Thanks Andrew.

Re: Filter out data

belromvar — Fri, 01 Sep 2017 12:12:40 GMT

Can you explain me how does it work, why do you need to "Load" twice?

Re: Filter out data

effinty2112 — Fri, 01 Sep 2017 12:32:43 GMT

Hi Belia,

Is this the statement you're asking about?

LOAD

User

Where CntDate <=2;

LOAD

User,

count(DISTINCT Date) as CntDate

Resident Data Group by User;

This is called a preceding load. The bottom half loads first then the resulting records are passed up to the top statement.

LOAD

User,

count(DISTINCT Date) as CntDate

Resident Data Group by User;

Returns

User	CntDate
U1	4
U2	1
U3	1

then the top statement works on that result to return:

User
U2
U3

When we take the inner join of this table with your data table we remove data for all users except U2 & U3.

Regards

Andrew

Re: Filter out data

belromvar — Fri, 01 Sep 2017 13:35:07 GMT

Perfect explanation.

Another question: I do not understand how the inner join changes if you do not require an aggr function. Lets say I just want the entries where date betweeen x and y.

Re: Filter out data

effinty2112 — Fri, 01 Sep 2017 13:58:58 GMT

Hi Belia,

I don't understand your question. Regarding Aggr please be aware that this function is not available in script. It is a UI function that creates an in-memory array like a straight table with one expression and one or more dimensions.

When you talk of date between x and y then you should add a where condition when loading your data like

WHERE Date >= x AND Date <=y.

If you want to consider users whose number of distinct dates lie between two values then

LOAD

User

Where CntDate >= x AND CntDate <=y;

LOAD

User,

count(DISTINCT Date) as CntDate

Resident Data Group by User;

Regards

Andrew

Re: Filter out data

belromvar — Mon, 04 Sep 2017 07:00:48 GMT

Hi Andrew,

Actually what I want to try is to filter by date WHERE Date >= '2017-08-11' AND Date <='2017-08-13' but filter out all the entries of the user´s that don´t fulfill the condition. In this case, U1 has a good entry but it should be discriminated and return only the records for U2 and U3. I was thinking something like calculating the Min and Max Dates for each User, but my example based in your previous answer doesnt work. Any suggestions?

I tried with:

Inner Join (Data)

LOAD

User, Date

WHERE MinD > '2017-09-01' and maxD < '2017-09-05';

LOAD User, Datum, min(date(Date)) as MinD, max(Date) as MaxD

Resident Data Group by User;

User Area Date

U1 A1 2017-08-11

U1 A2 2017-08-12

U1 A1 2017-08-13

U1 A1 2017-08-14

U2 A1 2017-08-12

U3 A5 2017-08-12

Re: Filter out data

belromvar — Mon, 04 Sep 2017 07:30:53 GMT

I got it. The problem was loading Date in the Outer Load. Can you explain, how this affects the load execution?