topic Re: Exists/ not Exists using flags in QlikView

Exists/ not Exists using flags

quiquehm — Thu, 15 Oct 2015 16:29:18 GMT

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 :

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

Re: Exists/ not Exists using flags

Gysbert_Wassenaar — Thu, 15 Oct 2015 16:34:23 GMT

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];

Re: Exists/ not Exists using flags

quiquehm — Thu, 15 Oct 2015 21:26:20 GMT

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

Re: Exists/ not Exists using flags

Gysbert_Wassenaar — Fri, 16 Oct 2015 07:36:45 GMT

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,

Re: Exists/ not Exists using flags

quiquehm — Fri, 16 Oct 2015 14:56:52 GMT

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