Limit dimension values inside Load script using Inline Table

[#1]

Hi,

I am trying to create an inline table (TESTTABLE) that will have select sales order values from table Delivery, field VGBEL. The table will also have a new field column named exclusion. I am trying to do this inside the load script, but whenever I reload the script and create the inline table, I end up with all 8,000 sales orders rather than the select few that I wanted. The other issue is that the sales orders inside my new table are not connecting to field VGBEL inside the delivery table.

Dimension:VGBEL

Table: Delivery

Below is my code

UNQUALIFY*;

TESTTABLE:

LOAD * INLINE [

     VGBEL,Exclusion

     801543678, X

     801564593, X

     ];

QUALIFY*;

[vishsaggi]

Can you elaborate a little please. Can you share your sample app and your expected output for quick help?

[vinieme12]

Post the complete script

[swuehl]

QUALIFY *;

will create qualified field names in your subsequent table loads, that's probably why your fields are not linked (they have different names in the data model). Check the table view.

If you want to remove the flagged field values from your delivery table load, you can use WHERE NOT EXISTS():

LOAD

TESTTABLE:

LOAD VGBEL as VGBEL_EXCLUSION INLINE [

     VGBEL,Exclusion

     801543678, X

     801564593, X 

     ];

LOAD VGBEL, OtherFields

FROM DeliveryTable

WHERE NOT EXISTS(VGBEL_EXCLUSION, VGBEL);

DROP TABLE TESTTABLE;

If you want to keep only these values, use instead:

LOAD

TESTTABLE:

LOAD VGBEL  INLINE [

     VGBEL,Exclusion

     801543678, X

     801564593, X 

     ];

LOAD VGBEL, OtherFields

FROM DeliveryTable

WHERE EXISTS( VGBEL);

DROP TABLE TESTTABLE;

[#5]

Hi guys,

Sorry for not elaborating a little more before on what I am trying to achieve. I would like to create a new inline table called TESTTABLE. This table will have 2 fields. One of the fields VGBEL exists already inside another table (Delivery). The other field Exclusion is just a made up field that I am creating with no links to any other table. My issue is that I want only two specific values to appear inside my inline table for field VGBEL and not all 8,000+ values that it currently has inside the delivery table. At the same time, I want the field VGBEL inside my new table to be linked to the VGBEL field inside the delivery table.

Please see below of how its currently connected;

This is how it appears in the layout;

The new table should only have the two values I specified in below script.

UNQUALIFY*;

TESTTABLE:

LOAD * INLINE [

     VGBEL,Exclusion

     801543678, X

     801564593, X

     ];

QUALIFY*;

Final result should be like this when one of the values is selected from the new table.

[rahulpawarb · Accepted solution]

Hello Omar,

Hope you are doing well!

By Design QlikView join is FULL OTTER JOIN i.e. It will select matching as well as non-matching data from both the tables. In your case it is pulling matching as well as non-matching VGBEL values from both the table. That's why you are getting all of the rows in your table.

To get only two rows you can do below settings and solve your problem.

1. Go to the properties of table and select Presentation tab.

2. In this select Exclusion field.

3. Check the the Omit Rows Where Field is NULL option.

4. Click OK.

Regards!

Rahul

[vinieme12]

Use Keep (right or left depending on your load order!)

Read more here

Understanding Join, Keep and Concatenate

[#8]

Thank you Rahul, that gave me the results I needed, but I wanted to know if there was a way to do it without having to to go inside the table box properties and omit the null values?

[#9]

Vineeth,

Thank you for your response. Can you please assist me in updating my code using KEEP. When I used it inside my load script, I still received all 8K plus values for field VGBEL when I created the INLINE table. I only want to see the 2 values that I specified!

This is how I wrote it;

Load order is in the following sequence;

1. Table Delivery

2. Inline Table Testtable

UNQUALIFY*;

TESTTABLE:

left keep LOAD * INLINE [

     VGBEL,Exclusion

     801543678, X

     801564593, X

     ];

QUALIFY*;

[vinieme12]

What are you using Qualify for??

This is how I would do it, first load FACT table, then Inner Keep load the values you want to restrict load to.

FACT:

Load *

FROM FACT_SOURCE;

INNER KEEP

Load

VGBEL,Exclusion

From ExclusionList;

IF it is still unclear, post the complete script