How do I load only part numbers that exist in Excel table

[Anonymous]

Hello, I am new to QlikView. I am trying to build a report that loads only part numbers listed in an Excel (Key) table from multiple QVD files. For example, the report will only pull data (part numbers) from the QVD tables that exists in the Excel table. All other data to be excluded due to size (i.e. 43K PN's in table to keep versus a total of 400K PN's)

My report utilizes seven different QVD's. Our orders data is stored by year (i.e. Orders Current year/last year, Orders 2016, Orders 2015, Orders 2014 etc....).  I only want a select group of part numbers that we will report on in terms of order value. We will regularly monitor this Key table and add;/remove parts as needed, To try and summarize:

Excel Table Key:

Material    Group

1A              1

2A              1

3B              2

4B              3

5B              1

Orders Table (Seven separate tabs for each year):

Material        Customer      Organization       Seller         Order Value  

1A                 Yellow           Steel                   Mary          $5                     

2A                 Blue               Wood                 Joe             $10                   

5X                 Black            Liquid                  Victor          $0                     

7Q                 Purple           Liquid                  Mark           $0                    

3B                 Yellow           Steel                   Mary           $7                    

4B                 Red               Gold                    Sue            $25                  

5B                Red               Gold                    Sue            $50                   

Desired Result:

Material        Customer      Organization       Seller         Order Value    Group

1A                 Yellow           Steel                   Mary          $5                       1

2A                 Blue               Wood                 Joe             $10                    1

3B                 Yellow           Steel                   Mary           $7                      2

4B                 Red               Gold                    Sue            $25                    3

5B                 Red               Gold                    Sue            $50                    1

I hope this explains the question but have been struggling with this for many hours by using  "Where Exists" but doesn't seem to be working correctly. Thank you in advance! Stephen

[felipedl]

HI Stephen,

Use the exists statement

Example:

Table:

load * Inline

[

Material,Group

1A,1

2A,1

3B,2

4B,3

5B,1

];

data:

Load * Inline

[

Material,Customer,Organization,Seller, Order Value

1A,Yellow ,  Steel,   Mary,  $5   

2A,Blue,Wood, Joe,     $10 

5X ,Black,    Liquid , Victor,  $0   

7Q, Purple,   Liquid , Mark,   $0  

3B, Yellow,   Steel,   Mary ,  $7  

4B, Red,Gold,    Sue,    $25

5B,Red,Gold,    Sue ,   $50

]

where exists(Material);

This code will only load the Materials field that appear in the "Table" table.

As a result, you'll get

Material	Group	Customer	Organization	Seller	Order Value
2A	1	Blue	Wood	Joe	$10
5B	1	Red	Gold	Sue	$50
4B	3	Red	Gold	Sue	$25
1A	1	Yellow	Steel	Mary	$5
3B	2	Yellow	Steel	Mary	$7

[Anonymous]

Thank you for the quick response Felip!

I tried doing this by using the Insert/Load Statement/Load Inline, but observe two limitations. First the Excel table  within the "Inline Data Wizard" limits me to a total of 40K line items - ie. it does not let me account for all 43K PN's . I loaded the 40K PN's to test it, however it then loads this data in to the script - only about 4K items are listed? I don't want to reload data just yet to test it as it takes several hours to refresh my data over the network so thought I would ask first to make sure I understand. Thanks again, Stephen

[felipedl · Accepted solution]

Hi Stephen,

THe inline was just for example purpose.

You can load your excel file into a table, like this:

ExcelTable:

Load

     #KeyField,

     Field1,

     Field2

     // and so on

FROM [Excel File]

OrderTable:

Load *

FROM [Whatever]

where exists(#KeyField);

// by this step, you need to make sure that the #KeyField exists in both tables, as the example "Material" field)

By that, you should get what you need, only the orders that have the correspondent #KeyField in the ExcelTable will be loaded.

Felipe.

[sasiparupudi1]

Another solution may be doing left join

load * Inline

[

Material,Group

1A,1

2A,1

3B,2

4B,3

5B,1

];

left join

Load * Inline

[

Material,Customer,Organization,Seller, Order Value

1A,Yellow ,  Steel,   Mary,  $5  

2A,Blue,Wood, Joe,     $10

5X ,Black,    Liquid , Victor,  $0  

7Q, Purple,   Liquid , Mark,   $0 

3B, Yellow,   Steel,   Mary ,  $7 

4B, Red,Gold,    Sue,    $25

5B,Red,Gold,    Sue ,   $50

[Anonymous]

Ok, that seemed to work better and the refresh came back with a much smaller set of data I was hoping for. Thank you very much for the quick and efficient response! Stephen!

[Anonymous]

Thank you for the alternate solution Sasidhar. I may also give this a try just to compare the results! In my brief experience with QV, I am learning that there are sometimes more than one way to get what we are after and really appreciate both you and Felip taking time to help me! Stephen