Tips using Qlik functiom

[sidhiq91]

Hello Qlik experts,

I am looking for some interesting tips and tricks using qlik functions that u guys might have experienced in your projects.

Since it is weekly activity for me i have already gone through various links and tips and tricks posted here in our community.

Something looking for out of the box.

Thanks in advance

[Andrei_Cusnir · Accepted solution]

Hello,

I am not 100% sure if you are looking for specific tip for a specific function, however one tip that really helped me when working with Qlik Functions is breaking complex expressions into smaller parts that I can test individually and then combining the results of each individual test. 

Allow me to elaborated further in detail to my example:

Let's assume that you need to build a complex expression that does a simple currency conversion. Here is an example from another post for a Data load editor script:

[...]

Num(Cost/PICK(Match(Currency, 'USD', 'MXN', 'CAD'), 1, 20.68140, 1.30320), '#,##0.00;-#,##0.00') as Cost

[...]

Now, what this expression does, is taking the loaded cost, checks if it is USD or not and if it is not USD it makes the conversion by dividing the cost value. Essentially, it converts all the loaded values to USD.

As an expression, it is considered to be an complex one, so building the entire thing from scratch is going to be hard. A simple solution is to break it in smaller parts such us:

1. PICK(...) // Pick the conversion rate based on the reading currency

2. Cost/PICK(...) // Evaluate the expression 

3. Num([...],  '#,##0.00;-#,##0.00') // Apply needed number format

Now that we know what needs to be done, you can test the expressions one by one in a Straight table:

1. Create a Straight table chart

2. Add dimension "Cost" and another one "Currency"

3. Add measure and use only "Pick(...)"

4. As soon as Pick(...) is ready, create a new measure to test Cost/Pick(...)

5. As soon as the new expression is ready, create a new one to test the number formatting

6. When you are 100% sure that your outcome is correct, you will end up with the complete expression that you can use on its own within the chart, or in Data load editor to generate calculated dimensions.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members.