Inner Keep Load - issues

[ranibosch]

Hi, I have the following script:

//filter to show the latest BOM version only

[Latest Version of Certified BOMS]:

     Inner Keep LOAD

                                   [BOM No],

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

     Resident [Certified BOMS]

     Group By [BOM No];

     DROP Table [Latest Version of Certified BOMS];

My table looks as follows:

BOM No	BOM Item No	BOM UOM	BOM Version	Consumable	Quantity	Unit of Measure Code
155	PBWCLR/750	CASE		0	10	BAG
155	PCWWN17	CASE		0	1	BOX
155	PEA099	CASE		0	3	KG
155	PLFPEA099	CASE		0	10	EA
155	PLFPEA099B	CASE		0	10	EA
155	PLP03	CASE		0	11	EA
155	PLWENDWHITE03	CASE		0	1	EA
2783	FRULOL001	CASE		0	0,3	KG
2783	FRULOL001	CASE	1	0	0,3	KG
2783	L100X107-0002	CASE		0	0,01	KG
2783	L100X107-0002	CASE	1	0	10	EA
2783	PCWWC03	CASE		0	1	BOX
2783	PCWWC03	CASE	1	0	1	BOX
2783	PLWENDWHITE03	CASE		0	1	EA
2783	PLWENDWHITE03	CASE	1	0	1	EA

I want to get the following result:

     1. Bring the latest version of the BOM NO

          (BOM NO 2783's latest version is 1)

     2. if the version of the BOM NO is blank, bring the BLANK.

          (BOM NO 155 has no versions, so bring the blank detail)

[sunny_talwar]

So for 2783, bring all rows where BOM Version is 1 and for 155 bring all rows because all of them are null?

[Gysbert_Wassenaar]

Perhaps like this:

LEFT JOIN ([Latest Version of Certified BOMS])

LOAD

     [BOM No],

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

     1 as IsLatest

Resident

     [Certified BOMS]

Group By

     [BOM No];

[Truly Latest Version of Certified BOMS]:

LOAD

     *,

      [BOM No] as [BOM No LookUp],

RESIDENT

     [Latest Version of Certified BOMS]

WHERE

     IsLatest = 1

     ;

CONCATENATE ([Truly Latest Version of Certified BOMS])

LOAD

     *

RESIDENT

     [Latest Version of Certified BOMS]

WHERE

     NOT Exists([BOM No LookUp],[BOM No])

     ;

DROP FIELDS IsLatest, [BOM No LookUp];

DROP Table [Latest Version of Certified BOMS];

[Peter_Cammaert · Accepted solution]

I don't think Version 0 will exist in your table (or if it does, use Version No -1).

So maybe it's as simple as replacing all blank Version Nos with 0 during the initial load of table [Certified BOMS] from the external source.

Then your INNER KEEP code will work.

[ranibosch]

That sounds if it might work.

how do I write code for this? To replace blank with 0 for example?

[sunny_talwar]

Should be just like this

If(Len(Trim([BOM Version])) = 0, 0, [BOM Version]) as [BOM Version]

[Gysbert_Wassenaar]

Or just Alt([BOM Version],0) as [BOM Version]