Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
jhlovenc
Creator II
Creator II

Append field into table based on another table

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;

1 Solution

Accepted Solutions
Oleg_Troyansky
MVP & Luminary
MVP & Luminary

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!

View solution in original post

2 Replies
Oleg_Troyansky
MVP & Luminary
MVP & Luminary

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!

View solution in original post

jhlovenc
Creator II
Creator II

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.