topic Re: Convert to set analysis in QlikView

Convert to set analysis

gauravgg — Wed, 08 Mar 2017 08:08:44 GMT

I want to convert the following expression in to set analysis

Sum(If( Aggr(nodistinct Min(Datetime), BusinessID) = Datetime, OpeningBalance))

Re: Convert to set analysis

jonathandienst — Wed, 08 Mar 2017 08:22:38 GMT

This may not be suitable for set analysis - you will still need to loop over all the BusinessID values to determine the minimum date so the performance may not be a lot better than the Aggr(). This better done with a flag value derived in the load script.

Map_MinDates:

Mapping LOAD BusinessID,

Min(DateTime) as MinDateTime

Resident Data

Group By BusinessID;

Join(Data)

LOAD BusinessID,

Datetime,

if(Datetime = ApplyMap('Map_MinDates', BusinessID), 1, 0) as MinDateFlag

Resident Data;

Then the expression becomes:

Sum({<MinDateFlag = {1}> OpeningBalance)

Change the tablename above (Data) to the correct name for your model.

Re: Convert to set analysis

gauravgg — Wed, 08 Mar 2017 08:35:15 GMT

Thanks for the reply

But is it possible using set analysis only, can you help me ?

Thanks in advance

Re: Convert to set analysis

Anil_Babu_Samineni — Wed, 08 Mar 2017 08:40:38 GMT

Try this,

I don't think whether nodistinct require for you

Aggr(Sum({<Datetime = {'$(=Min(Datetime))'}>}OpeningBalance),BusinessID)

We can help you if you provide data / application

Re: Convert to set analysis

jonathandienst — Wed, 08 Mar 2017 08:43:12 GMT

I don't think it can be done practically using set analysis, but I would be interested to see if anyone can prove me wrong. And even if it is possible, it will be complex and unlikely to perform materially better than a sum(if())

Re: Convert to set analysis

PrashantSangle — Wed, 08 Mar 2017 08:43:43 GMT

Hi,

As you know set analysis evaluate set on chart level not on row level, there fore even if we convert it into set analysis still it not going work properly.

So i think jonathan suggestion is perfect solution for you.

Regards,

Prashant

Re: Convert to set analysis

jonathandienst — Wed, 08 Mar 2017 08:45:32 GMT

Anil

The Min(Datetime) in that set expression is the minimum for all BusinessID's. I think the requirement is for the Min(Datetime) be the earliest Datetime for each BusinessID.

Re: Convert to set analysis

Anil_Babu_Samineni — Wed, 08 Mar 2017 08:47:55 GMT

Let him explain more. I got his intention. That'f for i liked it your first reply. Anyhow, we can try different way if he prove himself or else we can suggest him as your first response

Re: Convert to set analysis

gauravgg — Wed, 08 Mar 2017 09:04:56 GMT

Jonathan , your reply was helpful

But i have a qliksense qvf in which i have a Binary load data model , i cannot make changes in data load editor .

So , i am trying to do it on the presentation layer

Re: Convert to set analysis

gauravgg — Wed, 08 Mar 2017 10:34:35 GMT

I tried this , but it is not giving me the value

Re: Convert to set analysis

Anil_Babu_Samineni — Wed, 08 Mar 2017 10:40:20 GMT

It won't work for you. Try with jontydkpi or else

stalwar1‌ mrkachhiaimp‌ marcowedel‌

Re: Convert to set analysis

sunny_talwar — Wed, 08 Mar 2017 10:47:25 GMT

May be this:

Sum({<BusinessID = {"=Min(TOTAL Datetime) = Datetime"}>} OpeningBalance)

Re: Convert to set analysis

gauravgg — Wed, 08 Mar 2017 10:51:44 GMT

sunny ,it is not working

Re: Convert to set analysis

sunny_talwar — Wed, 08 Mar 2017 10:59:47 GMT

Would you be able to provide a sample?

Re: Convert to set analysis

MK_QSL — Wed, 08 Mar 2017 11:51:40 GMT

Don't know how to convert this in set analysis but you can use below as well..

=FirstSortedValue(DISTINCT Aggr(SUM(OpeningBalance),BusinessID,Datetime),Datetime)

instead of this

Sum(If( Aggr(nodistinct Min(Datetime), BusinessID) = Datetime, OpeningBalance))

Re: Convert to set analysis

MarcoWedel — Thu, 09 Mar 2017 23:00:17 GMT

please post some sample data to test with as well as your expected result.

thanks

regards

Marco