topic Re: Substring - Set Analysis in App Development

Substring - Set Analysis

tim_m — Mon, 16 Apr 2018 06:36:43 GMT

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

Re: Substring - Set Analysis

petter — Mon, 16 Apr 2018 07:07:21 GMT

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

marcus_sommer — Mon, 16 Apr 2018 07:08:59 GMT

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

- Marcus

Re: Substring - Set Analysis

tim_m — Mon, 16 Apr 2018 07:13:41 GMT

Marcus, Thank you 🙂

Re: Substring - Set Analysis

tim_m — Mon, 16 Apr 2018 07:17:03 GMT

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

Re: Substring - Set Analysis

tim_m — Mon, 16 Apr 2018 07:23:19 GMT

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

Re: Substring - Set Analysis

petter — Mon, 16 Apr 2018 07:36:23 GMT

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

......

;