Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
ranibosch
Creator
Creator

Left join with multiple conditions

Hi, would like to left join to a table with multiple conditions, but I have no idea how to write the script for that.

I have the following 3 tables:

1. BOM Lines:

BOM Lines.No_BOM Lines.Production BOM No_BOM Lines.QuantityBOM Lines.Unit of Measure CodeBOM Lines.Version Code
MARSH00161306636040,0176KG5
MARSH00161306636040,059KG
MARSH00161306636040,059KG1
OATS01561306636040,1381KG3
OATS01561306636040,1558KG4
OATS01561306636040,1618KG5

2. BOM Header:

Production BOM Header.BOM NoProduction BOM Header.BOM UOMProduction BOM Header.Status
6130663604CASE2

3. BOM Version Header:


Production BOM Version.Production BOM No_Production BOM Version.StatusProduction BOM Version.Unit of Measure CodeProduction BOM Version.Version Code
61306636043CASE1
61306636043CASE2
61306636041CASE3
61306636041CASE4
61306636040CASE5

I would like to join the BOM STATUS (yellow column) to the BOM LINES table.

I have the following conditions of which tables' status it must take (BOM HEADER / BOM VERSION HEADER):

a. If the VERSION CODE = BLANK in the BOM LINES TABLE. Then join the Status column from the BOM HEADER table.

b. if the VERSION CODE is not BLANK, then look for the status in the BOM VERSION HEADER table.

    THE BOM NO & VERSION CODE MUST MATCH between the BOM LINES table & BOM VERSION HEADER table.



I hope that makes sense and that I can get a solution.

Thanks in advance!

1 Solution

Accepted Solutions
ranibosch
Creator
Creator
Author

//********************************************************************************************************************************************************************

//********************************************************************************************************************************************************************

//bring in the BOM Lines of all versions of BOM's

[TEMP BOM Lines]:

LOAD

"Production BOM No_" as [BOM No],

    "Version Code" as [BOM Version],

    "No_" as [Item No],

    "Unit of Measure Code",

    Quantity,

    Consumable;

SQL SELECT

"Production BOM No_",

    "Version Code",

    "No_",

    "Unit of Measure Code",

    Quantity,

    Consumable

FROM $Production BOM Line";

//create the key field between the bom header and lines

[BOM Lines]:

LOAD

[BOM No],

    [BOM Version],

    [BOM No]&'-'&[BOM Version] as [BOM KEY],

    [Item No],

    "Unit of Measure Code",

    Quantity,

    Consumable

Resident [TEMP BOM Lines];

DROP Table [TEMP BOM Lines];

//********************************************************************************************************************************************************************

//*******************************************************************************************************************************************************************

//bring in the BOM VERSION HEADERS

[TEMP BOM HEADERS]:

LOAD

"Production BOM No_",

"Version Code",

    "Unit of Measure Code" as [BOM UOM],

    Status;

   

SQL SELECT

"Production BOM No_",

    "Version Code",

    "Unit of Measure Code",

    Status

FROM $Production BOM Version";

//bring in the BOM HEADERS and CONCATENATE with the BOM VERSIONS table

Concatenate ([TEMP BOM HEADERS])

LOAD

"No_" as "Production BOM No_" ,

"Version Nos_" as "Version Code",

    "Unit of Measure Code" as [BOM UOM],

    Status;

SQL SELECT

"No_",

    "Unit of Measure Code",

    Status,

    "Version Nos_"

FROM $Production BOM Header";

//create a bom key in order to join to the bom lines table

Left Join ([BOM Lines])

LOAD

    "Production BOM No_"&'-'&"Version Code" as [BOM KEY],

    [BOM UOM],

    Status

Resident [TEMP BOM HEADERS];

DROP Table [TEMP BOM HEADERS];

//********************************************************************************************************************************************************************

//********************************************************************************************************************************************************************

//filter through the newly created BOM Lines table to show only the BOM's with status = 1

[Certified BOM's]:

LOAD

[BOM No],

    [BOM Version],

    [Item No],

    "Unit of Measure Code",

    Quantity,

    Consumable,

    [BOM UOM],

    Status

Resident [BOM Lines]

Where Status = 1;

DROP Table [BOM Lines];

//filter to show the latest BOM version only

[Latest Version of Certified BOM's]:

Inner Keep LOAD

[BOM No],

    max([BOM Version]) as [BOM Version]

   

Resident [Certified BOM's]

Group By [BOM No];

DROP Table [Latest Version of Certified BOM's];

View solution in original post

7 Replies
olivierrobin
Specialist III
Specialist III

hello

you have to create key : status + version and join on this key

and create in table header a row with blank version

the question is ; which data do you have to put in this row

ranibosch
Creator
Creator
Author

Thanks, can you have a look as to why my script does not want to left join the the BOM LINES table?

//********************************************************************************************************************************************************************

//bring in the BOM Lines of all versions of BOM's

[BOM Lines]:

LOAD

"Production BOM No_",

    "Version Code",

    "Production BOM No_"&'-'&"Version Code" as [BOM KEY],

    "No_" as [Item No],

    "Unit of Measure Code",

    Quantity,

    Consumable;

SQL SELECT

"Production BOM No_",

    "Version Code",

    "No_",

    "Unit of Measure Code",

    Quantity,

    Consumable

FROM $Production BOM Line";

//********************************************************************************************************************************************************************

//bring in the BOM VERSION HEADERS

[TEMP BOM HEADERS]:

LOAD

    "Production BOM No_" as [BOM No],

    "Version Code" as [BOM Version],

    "Unit of Measure Code" as [BOM UOM],

    Status;

   

SQL SELECT

"Production BOM No_",

    "Version Code",

    "Unit of Measure Code",

    Status

FROM $Production BOM Version";

//bring in the BOM HEADERS and CONCATENATE with the BOM VERSIONS table

Concatenate ([TEMP BOM HEADERS])

LOAD

    "No_" as [BOM No],

    "Version Nos_" as [BOM Version],

    "Unit of Measure Code" as [BOM UOM],

    Status;

SQL SELECT

"No_",

    "Unit of Measure Code",

    Status,

    "Version Nos_"

FROM $Production BOM Header";

//create key in order to link the bom header table to the BOM lines table

left Join [BOM Lines]:

LOAD

    [BOM No]&'-'&[BOM Version] as [BOM KEY],

    [BOM UOM],

    Status

Resident [TEMP BOM HEADERS];

DROP Table [TEMP BOM HEADERS];

//********************************************************************************************************************************************************************

olivierrobin
Specialist III
Specialist III

at 1st sight, that's seems ok

do you have correct results when No and Version have values ?

ranibosch
Creator
Creator
Author

I get the correct results when I do not left join the BOM HEADERS to the BOM LINES.(when i keep it as a seperate table)

Then I have the 2 tables, with primary key called BOM KEY.

as soon as I try to LEFT JOIN to the BOM LINES, the BOM UOM & STATUS fields disappear.

olivierrobin
Specialist III
Specialist III

that means you left join isn't successful

try to see if the fields contains the right values (ie no spaces somewhere, same length, ...)

balkamal
Contributor II
Contributor II

Could you try the below script  which as same as what you have implemented with little adjustments.

first creation of key in BOM lines table

when the version code is blank replacing all the code with NA .Also as  you want status from BOM header only when the version code is blank so iassigned NA to all the BOMNO in BOM header Table

//********************************************************************************************************************************************************************

//bring in the BOM Lines of all versions of BOM's

[BOM Lines]:

LOAD

"Production BOM No_",

    "Version Code",

    "Production BOM No_"&'-'&if(len(trim("Version Code"))>0,"Version Code",'NA') as [BOM KEY],

    "No_" as [Item No],

   "Unit of Measure Code",

    Quantity,

    Consumable;

SQL SELECT

"Production BOM No_",

    "Version Code",

    "No_",

    "Unit of Measure Code",

    Quantity,

    Consumable

FROM $Production BOM Line";

//********************************************************************************************************************************************************************

//bring in the BOM VERSION HEADERS

[TEMP BOM HEADERS]:

LOAD

    "Production BOM No_" as [BOM No],

    "Version Code" as [BOM Version],

    "Unit of Measure Code" as [BOM UOM],

    Status;

SQL SELECT

"Production BOM No_",

    "Version Code",

    "Unit of Measure Code",

    Status

FROM $Production BOM Version";

//bring in the BOM HEADERS and CONCATENATE with the BOM VERSIONS table

Concatenate ([TEMP BOM HEADERS])

LOAD

    "No_" as [BOM No],

    'NA' as [BOM Version],

    "Unit of Measure Code" as [BOM UOM],

    Status;

SQL SELECT

"No_",

    "Unit of Measure Code",

    Status,

   "Version Nos_"

FROM $Production BOM Header";

//create key in order to link the bom header table to the BOM lines table

left Join [BOM Lines]:

LOAD

    [BOM No]&'-'&[BOM Version] as [BOM KEY],

    [BOM UOM],

    Status

Resident [TEMP BOM HEADERS];

DROP Table [TEMP BOM HEADERS];

Hope this helps.

ranibosch
Creator
Creator
Author

//********************************************************************************************************************************************************************

//********************************************************************************************************************************************************************

//bring in the BOM Lines of all versions of BOM's

[TEMP BOM Lines]:

LOAD

"Production BOM No_" as [BOM No],

    "Version Code" as [BOM Version],

    "No_" as [Item No],

    "Unit of Measure Code",

    Quantity,

    Consumable;

SQL SELECT

"Production BOM No_",

    "Version Code",

    "No_",

    "Unit of Measure Code",

    Quantity,

    Consumable

FROM $Production BOM Line";

//create the key field between the bom header and lines

[BOM Lines]:

LOAD

[BOM No],

    [BOM Version],

    [BOM No]&'-'&[BOM Version] as [BOM KEY],

    [Item No],

    "Unit of Measure Code",

    Quantity,

    Consumable

Resident [TEMP BOM Lines];

DROP Table [TEMP BOM Lines];

//********************************************************************************************************************************************************************

//*******************************************************************************************************************************************************************

//bring in the BOM VERSION HEADERS

[TEMP BOM HEADERS]:

LOAD

"Production BOM No_",

"Version Code",

    "Unit of Measure Code" as [BOM UOM],

    Status;

   

SQL SELECT

"Production BOM No_",

    "Version Code",

    "Unit of Measure Code",

    Status

FROM $Production BOM Version";

//bring in the BOM HEADERS and CONCATENATE with the BOM VERSIONS table

Concatenate ([TEMP BOM HEADERS])

LOAD

"No_" as "Production BOM No_" ,

"Version Nos_" as "Version Code",

    "Unit of Measure Code" as [BOM UOM],

    Status;

SQL SELECT

"No_",

    "Unit of Measure Code",

    Status,

    "Version Nos_"

FROM $Production BOM Header";

//create a bom key in order to join to the bom lines table

Left Join ([BOM Lines])

LOAD

    "Production BOM No_"&'-'&"Version Code" as [BOM KEY],

    [BOM UOM],

    Status

Resident [TEMP BOM HEADERS];

DROP Table [TEMP BOM HEADERS];

//********************************************************************************************************************************************************************

//********************************************************************************************************************************************************************

//filter through the newly created BOM Lines table to show only the BOM's with status = 1

[Certified BOM's]:

LOAD

[BOM No],

    [BOM Version],

    [Item No],

    "Unit of Measure Code",

    Quantity,

    Consumable,

    [BOM UOM],

    Status

Resident [BOM Lines]

Where Status = 1;

DROP Table [BOM Lines];

//filter to show the latest BOM version only

[Latest Version of Certified BOM's]:

Inner Keep LOAD

[BOM No],

    max([BOM Version]) as [BOM Version]

   

Resident [Certified BOM's]

Group By [BOM No];

DROP Table [Latest Version of Certified BOM's];