topic Re: Information query control in Management & Governance

Information query control

denisetrujillo — Wed, 28 Feb 2018 18:08:55 GMT

Hello everyone,

I need to have a control of the clients so I want to do the following:

I have an excel file which contains the keys of the companies

example:

Excel file

COMPANY

Demo1

Demo2

I want to link this information with the table of companies that I have in the database or qvd.

I want to indicate in the script is a condition in which you get the following: Yes the company code is not in the list of the excel file does not show information on the boards or graphics and send a message "Not Assigned the company "but everything from the script.

can you support me hic‌

Thank you...

Re: Information query control

hic — Thu, 01 Mar 2018 10:45:13 GMT

You can use the following as template: It tests whether each DB-Company exists in the Excel file or not.

ExcelCompanies:
Load
Company,
CompanyInExcel
From <ExcelFile> ;

DBCompanies:
Load
Company,
If(Exists(CompanyInExcel,Company), 'Listed Company', 'Non-listed Company') as CompanyStatus,
...
From <DBFile> ;

Re: Information query control

suepenick — Thu, 01 Mar 2018 18:18:42 GMT

Thanks Henric - I can use this all over the place!

Re: Information query control

denisetrujillo — Thu, 01 Mar 2018 19:16:52 GMT

Thanks for helping; however, I have another question once I create the field. in each of the dimensions and measures I have to add it with an "if" conditional?
or with a variable?

Re: Information query control

denisetrujillo — Thu, 01 Mar 2018 19:17:22 GMT

Thanks for helping; however, I have another question once I create the field. in each of the dimensions and measures I have to add it with an "if" conditional?

or with a variable?

Re: Information query control

hic — Fri, 02 Mar 2018 07:55:42 GMT

You should not use If(). Nor should you use a variable.

If you want it as dimension, you can use the field as it is. If you want to use it as condition in a calculation, you should use Set Analysis, e.g.:

Sum({$<CompanyStatus={'Listed Company'}>} Amount)

Re: Information query control

suepenick — Fri, 02 Mar 2018 13:29:24 GMT

I feel like this is definitely a learning moment:

1) Does the set analysis improve processing speed over an if statement?

2) in the Masters Summit someone recommended add a flag in the load script

If(Exists(CompanyInExcel,Company), 1,0) as CompanyStatusFlag,

then in the expression Sum(CompanyStatusFlag * Amount)

since then - this is what I have been doing since all the formulas are so simple

does set analysis improve processing over using the Flag * amount?

Re: Information query control

hic — Fri, 02 Mar 2018 13:48:02 GMT

For large data amounts - when you need the speed - Set Analysis is faster than Sum(Flag * Amount). For small data amounts Set Analysis can be slightly slower than Sum(Flag * Amount).

There is however a second reason why you shouldn't use Sum(Flag * Amount): In some odd cases, this can result in incorrect numbers, whereas Set Analysis always returns the correct number.

Flags can still be good though. You can use these in Set Analysis.

HIC

Re: Information query control

suepenick — Fri, 02 Mar 2018 13:51:35 GMT

Thanks for the clarification! Though I always check my formulas and the sum(Flag*Amount) has not failed yet.

I shall test the set analysis in one of my bigger qvws.

Re: Information query control

denisetrujillo — Fri, 02 Mar 2018 16:08:53 GMT

Thanks for helping!!