Information query control

[denisetrujillo]

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

[hic]

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

[suepenick]

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

[denisetrujillo]

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?

[denisetrujillo]

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?

[hic]

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)

[suepenick]

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?

[hic]

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

[suepenick]

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.

[denisetrujillo]

Thanks for helping!!