topic Re: Script wise Dimension in QlikView

Script wise Dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Guys,

I have the data as shown below,

Field	Customer	ID
A	C1	1
B	C2	2
C	C1	3
D	C2	1

Expected Output:

I want the no. of Fields which has same IDs. Both fields have same IDs but from different customers in the above bar chart. I've got the answer for this using expression, but I need to bring it as a dimension in script.

Please help.

Re: Script wise Dimension

sunny_talwar — Mon, 31 Jul 2017 13:41:18 GMT

Dimension

=Aggr(If(Count(TOTAL <ID> Customer) >= 2, Customer), ID, Customer)

Expression

Count(DISTINCT Customer)

Re: Script wise Dimension

Anonymous — Mon, 31 Jul 2017 13:47:22 GMT

No, Instead of using this in calculated dimensions, can we make it as dimension storing only the measure values in it?

Re: Script wise Dimension

sunny_talwar — Mon, 31 Jul 2017 13:48:24 GMT

Not sure I understand

Re: Script wise Dimension

Anonymous — Mon, 31 Jul 2017 13:58:16 GMT

Sunny, I'll explain my requirement in detail.

Assume I have 3 customers, each having some number of fields. When a customer is selected, I need to show the number of fields which are exactly same in other 2 customers by comparing the fields of selected customer.

To achieve this, I've used P() and E() function,

Count({<Customer=E(Customer),WithProp_ID=P(WithProp_ID)>}FieldName)

But if data increases, I was informed that these expressions in the front end may affect the performance(load time).

So I need to calculate those expressions in the script itself since it can increase the performance. Am I right? can we do like this?

or can I proceed with this method itself?

Re: Script wise Dimension

sunny_talwar — Mon, 31 Jul 2017 14:02:29 GMT

Doing something in script definitely improves performance, but not everything can be moved to the script. In your case where you plan to make selections, it might not be possible to move something to the script.... but then again, if this is just based on selection in customer and no other selection needs to be made or if made can be ignored, then you might be able to do this in the script.... it all depends on what you have and how you are executing this.

Re: Script wise Dimension

Anonymous — Mon, 31 Jul 2017 14:16:15 GMT

okay. Can we use set analysis expressions in the script, because I'm trying to use this in script.

Re: Script wise Dimension

sunny_talwar — Mon, 31 Jul 2017 14:20:44 GMT

Set analysis can only be used on the front end, not available in script. You can replicate set analysis using if statement.

Re: Script wise Dimension

Anonymous — Mon, 31 Jul 2017 14:25:57 GMT

Sunny, I've attached the sample data and QVW file with this. But could you help me to make this expression into a dimension?

Re: Script wise Dimension

sunny_talwar — Mon, 31 Jul 2017 15:13:31 GMT

Which expression.... attached qvw doesn't have any object in it? Which expression needs to be converted to a dimension?

Re: Script wise Dimension

Anonymous — Mon, 31 Jul 2017 15:14:44 GMT

I need this expression to be converted into a dimension,

Count({<Customer=E(Customer),WithProp_ID=P(WithProp_ID)>}FieldName)

Re: Script wise Dimension

sunny_talwar — Mon, 31 Jul 2017 15:31:11 GMT

Dimension

=Aggr(If(Only({1}Customer) <> Only(TOTAL Customer) and SubStringCount(Concat(DISTINCT TOTAL '|' &WithProp_ID & '|'), '|'&Only({1}WithProp_ID)&'|') = 1, Only({<Customer>}Customer)), Customer, WithProp_ID)

Expression

Count({<Customer>}FieldName)

But note that this is going to give you really bad performance, I have no idea why you would want to use this, but since you asked, here you have

Re: Script wise Dimension

Anonymous — Mon, 31 Jul 2017 16:04:14 GMT

Thank you.. it's looking great. But I asked you whether we can convert this expression into a dimension in the script and not in the chart. Is this possible that way?

Re: Script wise Dimension

sunny_talwar — Mon, 31 Jul 2017 16:06:52 GMT

I guess I am failing to understand the use case to answer this... may be someone else can pitch in because unless I know what is the use case, I might give a solution which may or may not work for your real scenario

Re: Script wise Dimension

Anonymous — Mon, 31 Jul 2017 16:12:04 GMT

Ok thank you so much Sunny. Shall I provide you the use case now? Use case in the sense, you're asking real time scenario for this.

Re: Script wise Dimension

sunny_talwar — Mon, 31 Jul 2017 16:18:35 GMT

I meant to say a detailed test case, in a sense are you going to be making other selection after the selection in Customer, because if you do, then it might make it nearly impossible to do in the script. On the other hand, if selecting customer is the only thing you are planning to do... then this might work... I don't know... because I am not sure what all the users are going to select and how the real data model looks like

Re: Script wise Dimension

Anonymous — Mon, 31 Jul 2017 16:24:46 GMT

Sunny, I've attached my QVW, 50% completed. Please have a look at my original post for it.

In reusability tab, I have my doubted scenarios.

Re: Script wise Dimension

Anonymous — Mon, 31 Jul 2017 20:33:42 GMT

Hello,

I may be too late for this thread. I must say its a bit confusing. I may be mistaken. but at least I tried 🙂

Based on the excel that you posted:

S2:

LOAD FieldName,

[Data type],

[Min Occurs],

[Max Occurs],

[M/C/O],

Length,

Customer

FROM

[..\Downloads\S2.xlsx]

(ooxml, embedded labels);

Join

Load FieldName,Count(FieldName) as ct

Resident S2

Group by FieldName;

Finaltable:

LOAD FieldName ,Customer

Resident S2

WHERE ct>1;

DROP Table S2;

Your chart Dimension will then be Cutomer, The expression would be Count(Customer)

Please have a look and I hope it helps.

Re: Script wise Dimension

Anonymous — Tue, 01 Aug 2017 10:58:32 GMT

Thanks for your help Imad..

Re: Script wise Dimension

Anonymous — Tue, 01 Aug 2017 11:00:00 GMT

Sunny, please find the attached QVW file and suggest an idea. since you don't know my data model, please have a look at it.