Syntax errors when joining tables in Script

[nburton78]

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;

[Stoyan_Terziev]

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.

[nburton78]

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

[Stoyan_Terziev]

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.

[nburton78]

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?  

[Stoyan_Terziev · Accepted solution]

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

[jonathandienst]

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.

[nburton78]

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?  

[nburton78]

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;