Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

timorbit
New Contributor III

Substring - Set Analysis

Hi Folks,

how it is possible to define a substring in an expression?

My data spitting out two values from each of my 1000 products and qlik sum up this two values. Simplified a double date input in Qlik. For example:

PRODUCT    VALUE      ACCOUNT

----------------------------------------------------------

BASIC          1000           A100

                     1000           A100_X

Each Product has two ACCOUNT numbers. I want to select only Values without the Suffix _X.

That implies a value of 1000 for my product BASIC and not 2000.


Please can you help me with my set analysis.

Thanks,

Tim

1 Solution

Accepted Solutions

Re: Substring - Set Analysis

Maybe something like this: sum({< ACCOUNT -= {"*_X"}>} VALUE)

- Marcus

6 Replies
MVP
MVP

Re: Substring - Set Analysis

How you select the none _X accounts depends on where you want to filter them out.

1) In a load script you simply use the LOAD .....    WHERE ACCOUNT Not(Like '*_X');

2) In a measure you can do this:

Only( {<ACCOUNT=-{"*_X"}>} ACCOUNT ) if the dimensions in the chart/table will return two accounts one of which will be filtered out.  Or in a regular aggregation like this: Sum( {<ACCOUNT=-{"*_X"}>} Sales)

3) In a dimension you have to do something like this:

Aggr(  Only( {<ACCOUNT=-{"*_X"}>} ACCOUNT ) , <dim1>,<dim2>,.... )

Re: Substring - Set Analysis

Maybe something like this: sum({< ACCOUNT -= {"*_X"}>} VALUE)

- Marcus

timorbit
New Contributor III

Re: Substring - Set Analysis

Marcus, Thank you :-)

timorbit
New Contributor III

Re: Substring - Set Analysis

Thank you Petter! Version 2) works in my application.

timorbit
New Contributor III

Re: Substring - Set Analysis

Now I want to insert the context in my load script. Please can you give me the exact implementation? I´m getting red errors.

MVP
MVP

Re: Substring - Set Analysis

If it is in a LOAD statement with a FROM clause it would be after the FROM:

LOAD

*

FROM "LIB://......" (....)

WHERE

   ACCOUNT Not( Like "*_X" );

If you a a SQL statement you could do a much more efficient load by doing the WHERE in the SQL SELECT itself - but then the syntax would be governed by the particular SQL dialect of the database you have connected to. You could still use it in the LOAD part in the preceding load though:

A) In the SQL part:

LOAD

  ....;

SQL SELECT

.....

FROM ....

WHERE

  ACCOUNT NOT LIKE '%_X';   // Microsoft SQL Server Transact SQL syntax

B) in the LOAD part of the preceding LOAD:

LOAD

*

WHERE

  ACCOUNT Not(Like "*_X");

SQL SELECT

......

;

Community Browser