Qlik Community

Qlik Sense Deployment & Management

Discussion board where members learn more about Qlik Sense Installation, Deployment and Management.

denisetrujillo
New Contributor II

Information query control

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

9 Replies
Employee
Employee

Re: Information query control

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
Contributor

Re: Information query control

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

denisetrujillo
New Contributor II

Re: Information query control

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
New Contributor II

Re: Information query control

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?

Employee
Employee

Re: Information query control

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
Contributor

Re: Information query control

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?

Employee
Employee

Re: Information query control

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
Contributor

Re: Information query control

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
New Contributor II

Re: Information query control

Thanks for helping!!

Community Browser