Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm having problems finishing this script and its making me crazy. I know its my syntax but I can't figure it out.
I'm trying to create 3 fields in my script. Below is the explanation of each field and how it's written in Excel.
InventoryShortage: Count the number of Components that were short per Parent
Excel Example: '=COUNTIFS(A:A,A2,F:F,"InventoryShort")
IssueType: Determine the type of issue based on the number of short components
Excel Example: '=IF(H2=0,"Workorderissue",IF(H2=1,"ComponentIssue",IF(H2>1,"MultipleComponents",0)))
LowerLevel: Return the Short component when the issue type is =1 and InventoryStatus is "InventoryShort"
Excel Example: '=IF(AND(F2="InventoryShort",G2=1),C2,"")
The first Table loads fine, however once it hits the second table I get errors
BOMS:
load*,
Order_Number__SDDOCO & Order_Type__SDDCTO & Line_Number__SDLNID as Concat,
if(COMPONENT_TOTAL<=QOH,'InventoryAvailable','InventoryShort')as InventoryStatus
;
SQL ...;
When I add these lines, it errors:
Shortage:
Load*,
count(Inventorystatus <'InventoryShort'> Concat) as InventoryShortages
resident BOMS;
drop table BOMS;
Final:
Load*;
If(InventoryShortages=0 , WorkOrderIssue',if(InventoryShortages=1,'ComponentIssue',if(InventoryShortages>1,'MultipleComponentIssue',''))) as IssueType,
if(InventoryStatus='InventoryShort' and InventoryShortages=1,IXITM__Component_Item_Number_Short,'') as LowerLevel
resident Shortage;
drop table Shortage;
(Edit as I forgot the filter)
Then we need to add this:
BOMS:
load*,
Order_Number__SDDOCO & Order_Type__SDDCTO & Line_Number__SDLNID as Concat,
if(COMPONENT_TOTAL<=QOH,'InventoryAvailable','InventoryShort')as InventoryStatus
;
SQL SELECT...;
LEFT JOIN (BOMS)
LOAD
Concat,
Count(Concat) as [Inventory Shortage]
RESIDENT BOMS
WHERE InventoryStatus='InventoryShort'
GROUP BY
Concat
;
Hey,
If you send me 1 row of your excel, I can help you write the QV code.
Probably this part:
Shortage:
Load*,
count(Inventorystatus <'InventoryShort'> Concat) as InventoryShortages
resident BOMS;
needs to look like that:
Shortage:
Load
count([Concat]) as InventoryShortages
resident BOMS
WHERE InventoryStatus='InventoryShort'
;
This will create a table with single column and single value for all Inventory Shortages.
I need some more information to help you get all of it right.
Like is it the Total Inventory shortages that you need or is the Inventory shortages per Store for example or per issue type?
Kind regards,
S.T.
I'm looking to count all shortages per distinct concat. So a concat can occur a number of times in the column, and I just want to know how many shortages occur. This is what I'm looking to do.
Concat | InventoryStatus | InventoryShortages |
A | InventoryAvailable | 1 |
A | InventoryShort | 1 |
B | InventoryShort | 2 |
B | InventoryShort | 2 |
C | InventoryAvailable | 0 |
C | InventoryAvailable | 0 |
Hey,
Could you try an approach like this one:
MapNoOfShortages:
MAPPING LOAD
Concat,
Count(Concat)
FROM [sample.xlsx]
(ooxml, embedded labels, table is [BOMS Table])
WHERE InventoryStatus='InventoryShort'
GROUP BY
Concat
;
BOMS:
LOAD Order_Number__SDDOCO,
Order_Type__SDDCTO,
Line_Number__SDLNID,
Concat,
IXKITL__Parent_Item_New,
IXITM__Component_Item_Number_Short,
IBSTKT__Stocking_Type,
COMPONENT_TOTAL,
QOH,
Scheduled_Pick_Date__SDPDDJ,
InventoryStatus,
ApplyMap('MapNoOfShortages', Concat, 0) as [Inventory Shortage],
IF(ApplyMap('MapNoOfShortages', Concat, 0)=0, 'Workorderissue'
, IF(ApplyMap('MapNoOfShortages', Concat, 0)=1,'ComponentIssue'
, 'MultipleComponents')) as [ISSUE TYPE],
IF(COMPONENT_TOTAL>QOH,IXITM__Component_Item_Number_Short,'') as Lowerlevel
FROM [sample.xlsx]
(ooxml, embedded labels, table is [BOMS Table])
;
Let me know if that works.
Kind regards,
S.T.
currently my script looks like this:
BOMS:
load*,
Order_Number__SDDOCO & Order_Type__SDDCTO & Line_Number__SDLNID as Concat,
if(COMPONENT_TOTAL<=QOH,'InventoryAvailable','InventoryShort')as InventoryStatus
;
SQL SELECT...;
Would I add this after my original BOMS table? replacing the "from sample" line with my sql?
(Edit as I forgot the filter)
Then we need to add this:
BOMS:
load*,
Order_Number__SDDOCO & Order_Type__SDDCTO & Line_Number__SDLNID as Concat,
if(COMPONENT_TOTAL<=QOH,'InventoryAvailable','InventoryShort')as InventoryStatus
;
SQL SELECT...;
LEFT JOIN (BOMS)
LOAD
Concat,
Count(Concat) as [Inventory Shortage]
RESIDENT BOMS
WHERE InventoryStatus='InventoryShort'
GROUP BY
Concat
;
You need a Group By for the query with Count:
Shortage:
Load ...
count(Inventorystatus <'InventoryShort'> Concat) as InventoryShortages
resident BOMS
Group By ...
You need to list all the fields except Concat in the LOAD and Group By clauses where I have placed the ellipses.
This works for counting the shortages perfectly. Now I need to be able to classify by the shortage count and then name the shorted component:
if(InventoryShortage=0, "WorkOrder", if "inventoryShortage=1,"ComponentIssue"....
and
If (InventoryShortage=1 and QOH<ComponenentTotal, ItemShortNumber, blank)
should this be done with a resident table?
I wrote the final table like this and it seems to be working, thank you for all the help and guidance!
Final:
Load*,
IF([Inventory Shortage]=1 and QOH<COMPONENT_TOTAL, IXITM__Component_Item_Number_Short,0) as LowerLevel,
IF([Inventory Shortage]=0,'WorkOrderIssue',if([Inventory Shortage]=1,'ComponentIssue',if([Inventory Shortage]>1,'MultipleComponentIssue','')))as IssueType
resident BOMS;
drop table BOMS;