Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tyember1
Contributor III
Contributor III

Measure involving Different Part Number UOM

Hello,

I am trying to divide Order Quantity by the UOM of the corresponding Item number. the item numbers ending in E are eaches, and without the E are cases. I need to divide the EA order quantity by the Case UOM. This is coming out of a database, so the above and below expressions are not working for me yet. Unsure of what to use to see this result.

Qlik Help.png

 

sum(IF(pr_codenum LIKE '*E', or_ordquant)) /

sum(IF(pr_codenum LIKE '*E',({1<pr_codenum-={'*E'}>} [Part Number UOM])))

 

 

Thank you,

Tyler

Labels (2)
1 Solution

Accepted Solutions
tyember1
Contributor III
Contributor III
Author

I ended up creating a table and picking out the numbers from the item description then joining to the Item Master.
ex of item description:

pr_codenum: 60020E pr_descrip: FR Smoked Pecans 1/1lb

pr_codenum: 60020 pr_descrip: FR Smoked Pecans 12/1lb

Item_Master:
LOAD
*,
pr_c2id as c2_id,
KeepChar(replace(pr_descrip,'W/','With'),'1234567890/') as 'Part Number UOM',
left(pr_codenum,5) as 'CombinedPartCode',
pr_caid as ca_id
FROM [lib://Extract QVDs/TL_Item_Master.QVD]
(qvd);

left join(Item_Master)
IM_2:
LOAD
left(pr_codenum,5) as 'CombinedPartCode',
(Subfield(KeepChar(replace(pr_descrip,'W/','With'),'1234567890/') ,'/',1)) as 'Case to EA UOM'
FROM [lib://Extract QVDs/TL_Item_Master.QVD]
(qvd);

 

Now that we have the same Case to EA UOM for both the items that end in E and items that do not, I can use an expression to convert the order count from EA to Case

View solution in original post

2 Replies
Marcos_rv
Creator II
Creator II

Try this:

sum( {< pr_codenum ={ '*E'} >}  or_ordquant)) /

count (   Total {< pr_codenum ={ '*E'} >} >} [Part Number UOM])

 

tyember1
Contributor III
Contributor III
Author

I ended up creating a table and picking out the numbers from the item description then joining to the Item Master.
ex of item description:

pr_codenum: 60020E pr_descrip: FR Smoked Pecans 1/1lb

pr_codenum: 60020 pr_descrip: FR Smoked Pecans 12/1lb

Item_Master:
LOAD
*,
pr_c2id as c2_id,
KeepChar(replace(pr_descrip,'W/','With'),'1234567890/') as 'Part Number UOM',
left(pr_codenum,5) as 'CombinedPartCode',
pr_caid as ca_id
FROM [lib://Extract QVDs/TL_Item_Master.QVD]
(qvd);

left join(Item_Master)
IM_2:
LOAD
left(pr_codenum,5) as 'CombinedPartCode',
(Subfield(KeepChar(replace(pr_descrip,'W/','With'),'1234567890/') ,'/',1)) as 'Case to EA UOM'
FROM [lib://Extract QVDs/TL_Item_Master.QVD]
(qvd);

 

Now that we have the same Case to EA UOM for both the items that end in E and items that do not, I can use an expression to convert the order count from EA to Case