Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ranibosch
Creator
Creator

Inner Keep Load - issues

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 NoBOM Item NoBOM UOMBOM VersionConsumableQuantityUnit of Measure Code
155PBWCLR/750CASE 010BAG
155PCWWN17CASE 01BOX
155PEA099CASE 03KG
155PLFPEA099CASE 010EA
155PLFPEA099BCASE 010EA
155PLP03CASE 011EA
155PLWENDWHITE03CASE 01EA
2783FRULOL001CASE 00,3KG
2783FRULOL001CASE100,3KG
2783L100X107-0002CASE 00,01KG
2783L100X107-0002CASE1010EA
2783PCWWC03CASE 01BOX
2783PCWWC03CASE101BOX
2783PLWENDWHITE03CASE 01EA
2783PLWENDWHITE03CASE101EA

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)

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

View solution in original post

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


talk is cheap, supply exceeds demand
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Creator
Creator
Author

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]


talk is cheap, supply exceeds demand