topic Re: Syntax errors when joining tables in Script in QlikView

Syntax errors when joining tables in Script

nburton78 — Sat, 16 Nov 2024 01:57:24 GMT

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;

Re: Syntax errors when joining tables in Script

Stoyan_Terziev — Wed, 13 Nov 2019 21:18:22 GMT

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.

Re: Syntax errors when joining tables in Script

nburton78 — Thu, 14 Nov 2019 12:48:59 GMT

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

Re: Syntax errors when joining tables in Script

Stoyan_Terziev — Thu, 14 Nov 2019 13:03:43 GMT

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.

Re: Syntax errors when joining tables in Script

nburton78 — Thu, 14 Nov 2019 13:27:34 GMT

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?

Re: Syntax errors when joining tables in Script

Stoyan_Terziev — Thu, 14 Nov 2019 13:33:21 GMT

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

Re: Syntax errors when joining tables in Script

jonathandienst — Thu, 14 Nov 2019 13:36:30 GMT

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.

Re: Syntax errors when joining tables in Script

nburton78 — Thu, 14 Nov 2019 16:13:33 GMT

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?

Re: Syntax errors when joining tables in Script

nburton78 — Thu, 14 Nov 2019 16:30:54 GMT

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;