Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tim_m
Partner - Contributor III
Partner - 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
marcus_sommer

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

- Marcus

View solution in original post

6 Replies
petter
Partner - Champion III
Partner - Champion III

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

marcus_sommer

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

- Marcus

tim_m
Partner - Contributor III
Partner - Contributor III
Author

Marcus, Thank you 🙂

tim_m
Partner - Contributor III
Partner - Contributor III
Author

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

tim_m
Partner - Contributor III
Partner - Contributor III
Author

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

petter
Partner - Champion III
Partner - Champion III

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

......

;