Set analysis of multiple statements

[#1]

I need to do a set analysis, but it's proving a bit tricky. I'm trying to return the Accounts.Name tagged as 'tier 1', which do not have an associated Activities.AccountID in the last 30 days.

My underlying table has 4 columns

Activity.CalendarDate

Activiities. AccountID

AccountTags.Name

Accounts.Name

In my example data I have 5 accounts and 2 of them are tagged as  'tier 1'. However, of the two 'tier 1' firms only Big Company llp has not had an actiivity within the last 30 days, so that is the result I would want displayed in a table, just the Accounts.Name.

Sample data is provided below

[alexandros17]

Here it is ...

Hope it helps

[srchilukoori · Accepted solution]

Using and script and set analysis.

SC

[#4]

The data model I gave you was slightly incorrect. Which is my fault. I've have attached the correct one

There is not a blank row for each account on each date. There is only one blank row for a date, if there are no activities at all for that day.

How would the expression differ for this new data model?

[srchilukoori]

It shouldn't make much difference if using the script as shown in the example.

SC

[#6]

I was referring to Alessandros solution. But I will try yours now

[#7]

srchilukoori, I am a bit of a Qlikview beginner, so how would I insert your load script into mine? I have a separate tab to load each object, so how would I amend my Accounts load script to fit yours in?

QUALIFY *;

UNQUALIFY Activity.AccountId;

UNQUALIFY AccountIdforTag;

Accounts:

LOAD Id AS Activity.AccountId,

    Id AS AccountIdforTag,

    IsDeleted,

    Name,

    Country__c

    WHERE EXISTS(Activity.AccountId,Id);

SQL SELECT *

FROM Account;

UNQUALIFY *;

[srchilukoori]

Please provide the script equivalent for the below in your app, so I can modify mine accordingly.

Data:

LOAD Activity.CalendarDate,

     Activities.AccountId,

     AccountTags.Name,

     Accounts.Name

FROM

(ooxml, embedded labels, table is Sheet1);

SC.

[#9]

It's a load directly from salesforce.com database, so there is no load script as you describe.

All i have is

CUSTOM CONNECT TO "Provider=SalesForceDLL.dll;F26;XUserId=ISdGAWRM..............................

[srchilukoori]

In that case, just do a resident load of the following fields/ logic after the data is loaded from SFDC.

Data2:

NoConcatenate

LOAD Distinct Accounts.Name,

     Today() - Max(Activity.CalendarDate) As [Days since Last Activity]

Resident Data

Where AccountTags.Name = 'Tier 1' and Len(Trim(Activities.AccountId))<>0

Group by Accounts.Name;

SC