Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
quiquehm
Contributor III
Contributor III

Exists/ not Exists using flags

Hi everyone,

I wondered if someone could give me some help here. I am struggling with something that I thought would be straight forward , but for whatever reason I don´t get what I need. I am trying to compare two lists of items from two files ( rev A and rev B files ) and detect which items from list A are missing in list B and viceversa ( basically I need to find items removed and items added in list B ) and create a field that I can use to select 'Added Items' or 'Removed Items'. I thought a very quick and simple way would be to add some flags to the script and use a rangesum function ( to "sum" the two columns, Flag_A + Flag_B ) and create the field I need ( what I called Flag_delta ).

This new field should have 3 values : -1,0,1 ( -1 for items existing only in list A , 0 for items existing in both lists , 1 for items existing only in list B )

See my script below :

Flags.png

For whatever reason the RANGESUM function is not giving me the value 0 , only values -1 and 1 . Do you know what I am doing wrong here ?

Any help is appreciated

Many thanks

Enrique

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

rangesum sums the values of the flags of the same record, not across records. What you want is

Delta:

LOAD [Equipement TAG],

     sum(Flag_A),

     sum(Flag_B)

     sum(rangesum(Flag_A, Flag_B))

Resident Temp

Group By [Equipement TAG];


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

rangesum sums the values of the flags of the same record, not across records. What you want is

Delta:

LOAD [Equipement TAG],

     sum(Flag_A),

     sum(Flag_B)

     sum(rangesum(Flag_A, Flag_B))

Resident Temp

Group By [Equipement TAG];


talk is cheap, supply exceeds demand
quiquehm
Contributor III
Contributor III
Author

Many thanks Gysbert ! it works great now. It also helps me to detect some problems with my source data files as it seems there are some duplications there ( you can see numbers lower than -1 or higher than 1 . There is something though I find strange and it happens to me as well in other Qview apps ( I am a beginner 🙂 , in the list box ( Equipment TAG ) the first value on the list is a blank value ( Null ? ) . Do you know why I have a blank value there ? Could it be a problem coming from my source file ( excel )?

Thanks again for the help

Rgds

Enrique

Flags_tables.png

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

The blank is probably an empty string or a value of on or more spaces. A listbox will not show real nulls. If you want to check for nulls and blanks you can use something like =if(len(trim( MyField )) <> 0, MyField, 'No Value') as MyField,


talk is cheap, supply exceeds demand
quiquehm
Contributor III
Contributor III
Author

Right Gysbert, I was checking the source files in detail and I noticed these blank field values when doing a Table transformation when loading the script . I simply removed these empty cells on this step, and then get rid of the blank value in the list box.

Thanks again

Rgds

BlankFieldValues.png