Append field into table based on another table

[Anonymous]

I have two tables, "Items" and "Contract_Items".

I am taking one field from Items - "Item_Number"

I am taking two fields from Contract_Items - "Item_Number' and "Division"

I want to load everything from Items and append in a field "Contract_Status" that is generated based on conditions from Contract_Items.

For example - If Item_Number 12345 in Items exist in Contract_Items with "Division" = '300', I have a field "Contract_Status" = 1

If 12345 exists in Contract_Items with Division = 300 and 345, "Contract_Status" = 2

If 12345 does not exist in Contract_Items in either Division 300 or 345, "Contract_Status" = 3

I tried this, but it just returned the original Division information:

LOAD

IF(Division='300',Dual(Division,1),

  IF(Division='345', Dual(Division,2),

  IF(Division<>'300', Dual(Division,3),

  IF(Division <>'345', Dual(Division,4))))) as CI_Status,

"Contract_Item",

    Division as CI_Division;

[Oleg_Troyansky · Accepted solution]

Your conditions involve values from multiple rows (for example, the Item should exist in both divisions, or in either one of the divisions). In order to calculate that, you should first aggregate your data from Contract_Items by Item and concatenate all available divisions:

Contract_Items_Sum:

load

     Item,

     concat(Division, '_') as AllDivisions

resident

     Contract_Items

group by

     Item

;

Now, you can prioritize your conditions from more strict to less strict and calculate the corresponding status. You can test the whole value of AllDivisions or test whether certain Division name is part of the value or not. Use the Index() function or the SubstringCount() function for that.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

[Anonymous]

Brilliant, thank you.  I do need to figure out how to use Index() and SubstringCount() functions, but you have definitely got me past the first hurdle.