topic Inner Keep Load - issues in QlikView

Inner Keep Load - issues

ranibosch — Fri, 23 Mar 2018 11:13:28 GMT

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)

Re: Inner Keep Load - issues

sunny_talwar — Fri, 23 Mar 2018 11:34:46 GMT

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

Re: Inner Keep Load - issues

Gysbert_Wassenaar — Fri, 23 Mar 2018 11:34:50 GMT

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

Re: Inner Keep Load - issues

Peter_Cammaert — Fri, 23 Mar 2018 11:52:24 GMT

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.

Re: Inner Keep Load - issues

ranibosch — Fri, 23 Mar 2018 12:34:56 GMT

That sounds if it might work.

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

Re: Inner Keep Load - issues

sunny_talwar — Fri, 23 Mar 2018 12:43:48 GMT

Should be just like this

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

Re: Inner Keep Load - issues

Gysbert_Wassenaar — Fri, 23 Mar 2018 13:35:59 GMT

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