topic Re: Set analysis of multiple statements in QlikView

Set analysis of multiple statements

Mon, 10 Mar 2014 14:02:53 GMT

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

Re: Set analysis of multiple statements

alexandros17 — Mon, 10 Mar 2014 14:22:51 GMT

Here it is ...

Hope it helps

Re: Set analysis of multiple statements

srchilukoori — Mon, 10 Mar 2014 14:50:13 GMT

Using and script and set analysis.

Re: Re: Set analysis of multiple statements

Tue, 11 Mar 2014 11:25:16 GMT

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?

Re: Set analysis of multiple statements

srchilukoori — Tue, 11 Mar 2014 13:52:29 GMT

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

Re: Set analysis of multiple statements

Tue, 11 Mar 2014 14:24:55 GMT

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

Re: Set analysis of multiple statements

Tue, 11 Mar 2014 14:46:34 GMT

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

Re: Set analysis of multiple statements

srchilukoori — Tue, 11 Mar 2014 15:07:57 GMT

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.

Re: Set analysis of multiple statements

Wed, 12 Mar 2014 10:17:09 GMT

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

Re: Set analysis of multiple statements

srchilukoori — Wed, 12 Mar 2014 14:44:40 GMT

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;