Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

Set analysis of multiple statements

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

1 Solution

Accepted Solutions
Highlighted
srchilukoori
Valued Contributor

Re: Set analysis of multiple statements

Using and script and set analysis.

SC

View solution in original post

9 Replies
Highlighted

Re: Set analysis of multiple statements

Here it is ...

Hope it helps

Highlighted
srchilukoori
Valued Contributor

Re: Set analysis of multiple statements

Using and script and set analysis.

SC

View solution in original post

Highlighted
Not applicable

Re: Re: Set analysis of multiple statements

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?

Highlighted
srchilukoori
Valued Contributor

Re: Set analysis of multiple statements

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

SC

Highlighted
Not applicable

Re: Set analysis of multiple statements

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

Highlighted
Not applicable

Re: Set analysis of multiple statements

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 *;

Highlighted
srchilukoori
Valued Contributor

Re: Set analysis of multiple statements

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.

Highlighted
Not applicable

Re: Set analysis of multiple statements

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..............................

Highlighted
srchilukoori
Valued Contributor

Re: Set analysis of multiple statements

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