topic Re: How do I load only part numbers that exist in Excel table in QlikView

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

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

felipedl — Thu, 19 Apr 2018 14:11:15 GMT

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

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

Anonymous — Thu, 19 Apr 2018 15:52:00 GMT

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

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

felipedl — Thu, 19 Apr 2018 17:50:25 GMT

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.

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

sasiparupudi1 — Thu, 19 Apr 2018 18:24:54 GMT

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

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

Anonymous — Thu, 19 Apr 2018 20:28:04 GMT

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!

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

Anonymous — Thu, 19 Apr 2018 20:30:22 GMT

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