Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Syntax errors when joining tables in Script

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;

 

11-13-2019 1-44-38 PM.jpg11-13-2019 1-44-48 PM.jpg

Labels (3)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

View solution in original post

8 Replies
Highlighted
Partner
Partner

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.

 

Highlighted
Contributor III
Contributor III

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.  

 

ConcatInventoryStatusInventoryShortages
AInventoryAvailable1
AInventoryShort1
BInventoryShort2
BInventoryShort2
CInventoryAvailable0
CInventoryAvailable0

 

Highlighted
Partner
Partner

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.

Highlighted
Contributor III
Contributor III

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?  

 

Highlighted
Partner
Partner

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

View solution in original post

Highlighted

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Contributor III
Contributor III

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?  

Highlighted
Contributor III
Contributor III

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;