topic Re: How to rewrite subfield statement without creating duplicate rows ? in App Development

How to rewrite subfield statement without creating duplicate rows ?

LaData — Mon, 27 Oct 2025 13:39:02 GMT

Here is my statement - this ge

SUBFIELD ( IF (TRIM( CALL_NUMBER ='0' , 'NEW SALES|ALL SALES' , 'ALL SALES' , '|'). AS 'FINAL SALES'

If 'NEW SALES' is selected must show 0

If 'ALL SALES' is selected also must show 0 and 1,2,3,4 .....

Re: How to rewrite subfield statement without creating duplicate rows ?

Or — Mon, 27 Oct 2025 13:51:11 GMT

Not sure what it is you're doing here, but it sounds like you want something like:

Main:

Load * From YourMainTable;

FilterTable:

Load * INLINE [

CALL_NUMBER, TYPE_OF_SALE

0, 'NEW SALES'

0, 'ALL SALES'

1, 'ALL SALES'

2, 'ALL SALES']; // Etc

Note that you can just use AutoGenerate() if you want to create a large number of call numbers rather than loading them inline.

Re: How to rewrite subfield statement without creating duplicate rows ?

marcus_sommer — Mon, 27 Oct 2025 14:43:49 GMT

Why not following the suggestion from Vegar?

Create a flag or dimension with 2 IF conditions. M... - Qlik Community - 2534385

Re: How to rewrite subfield statement without creating duplicate rows ?

LaData — Mon, 27 Oct 2025 15:19:38 GMT

INLINE LOAD creates 1 or 2 additional rows - also my data is loaded from SQL select and reloaded daily on a trigger

Re: How to rewrite subfield statement without creating duplicate rows ?

Or — Mon, 27 Oct 2025 20:20:29 GMT

This Inline does not create any rows in the original table. It simply creates a second table that can be used to filter the first.

Re: How to rewrite subfield statement without creating duplicate rows ?

Vegar — Tue, 28 Oct 2025 06:12:08 GMT

As earlier suggested, keeping the new dimension table associated, and not joined, with your main table will not increase the number of lines in your data model.

The total SUM(SALES_AMOUNY) without any selections will remain correct and identical to the value you get if selecting 'ALL SALES'.

The SUM(SALES_AMOUNT) will be correct when filtering on 'NEW SALES'.

When adding [FINAL SALES] to a table then the values for each row will be correct and the total will be correct (unless you change the totalling to sum of rows.

Unless you have a specific use case for your request, I honestly think it would be a better approach to skip the 'ALL SALES' value. Let your field be either 'NEW SALES' or 'REPEAT SALES', and instead teach the users that they get ALL SALES by filtering on none or both of these values. That is how I would have set this up in most scenarios.