Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Maybe something like this: sum({< ACCOUNT -= {"*_X"}>} VALUE)
- Marcus
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>,.... )
Maybe something like this: sum({< ACCOUNT -= {"*_X"}>} VALUE)
- Marcus
Marcus, Thank you 🙂
Thank you Petter! Version 2) works in my application.
Now I want to insert the context in my load script. Please can you give me the exact implementation? I´m getting red errors.
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
......
;