How to rewrite subfield statement without creating duplicate rows ?

[LaData]

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

[Or]

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. 

[marcus_sommer]

Why not following the suggestion from Vegar?

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

[LaData]

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

[Or]

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. 

[Vegar]

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.