Filter out data

[belromvar]

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.

[Anil_Babu_Samineni]

May be this?

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

[Anil_Babu_Samineni]

Or Front End may be this?

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

[effinty2112 · Accepted solution]

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

[antoniotiman]

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

[effinty2112]

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

[Kushal_Chawda]

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)

[belromvar]

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

[belromvar]

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

[effinty2112]

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