topic Limit dimension values inside Load script using Inline Table in QlikView

Limit dimension values inside Load script using Inline Table

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Limit dimension values inside Load script using Inline Table

vishsaggi — Fri, 27 Jan 2017 22:19:26 GMT

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

Re: Limit dimension values inside Load script using Inline Table

vinieme12 — Sat, 28 Jan 2017 07:20:45 GMT

Post the complete script

Re: Limit dimension values inside Load script using Inline Table

swuehl — Sat, 28 Jan 2017 10:53:51 GMT

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;

Re: Limit dimension values inside Load script using Inline Table

Mon, 30 Jan 2017 03:59:18 GMT

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.

Re: Limit dimension values inside Load script using Inline Table

rahulpawarb — Mon, 30 Jan 2017 05:36:24 GMT

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

Re: Limit dimension values inside Load script using Inline Table

vinieme12 — Mon, 30 Jan 2017 06:48:29 GMT

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

Re: Limit dimension values inside Load script using Inline Table

Mon, 30 Jan 2017 17:08:53 GMT

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?

Re: Limit dimension values inside Load script using Inline Table

Mon, 30 Jan 2017 17:39:01 GMT

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

Re: Limit dimension values inside Load script using Inline Table

vinieme12 — Mon, 30 Jan 2017 18:44:44 GMT

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

Re: Limit dimension values inside Load script using Inline Table

vinieme12 — Mon, 30 Jan 2017 18:47:35 GMT

This way you are only limiting rows shown in that particular tablebox not your QVW!

Re: Limit dimension values inside Load script using Inline Table

rahulpawarb — Tue, 31 Jan 2017 04:48:52 GMT

Hello Omar,

Thank you for considering my suggestion.

If you want to use JOIN or KEEP to get only two records from TESTTABLE table then you have to have INNER JOIN/RIGHT JOIN/INNER KEEP/RIGHT KEEP options by selecting TESTTABLE table in right side (for RIGHT KEEP/RIGHT JOIN). This will lead to filtration of records in the Delivery table. In this case Delivery table will only contain 2 records which are there in TESTTABLE table. Other records won't be available in Qlik. If this doesn't impact on your solution then go ahead and implement the JOIN/KEEP else go ahead natural JOIN option (stated in my prior comments).

Hope this makes sense.

Regards!

Rahul