topic Re: Join on table containing input fields - a bug ? in QlikView

Join on table containing input fields - a bug ?

Thu, 18 Apr 2013 19:17:46 GMT

Hi,

I have a simple table containing an input field. When that table is used as a source for join on another table, the data inside the input field changes to row numbers. Please see the attached qvw and let me know if there is a workaround possible to maintain the data along with the join.

Here's the script:

INPUTFIELD ProductThreshold;

ProductThresholds:

LOAD * INLINE [

ProductName, ProductThreshold

P1, -0.025

P2, -0.5

];

ProductDim:

LOAD * INLINE [

ProductName

];

LEFT JOIN(ProductDim)

LOAD

Resident

ProductThresholds;

DROP TABLE ProductThresholds;

Re: Join on table containing input fields - a bug ?

Gysbert_Wassenaar — Thu, 18 Apr 2013 19:27:59 GMT

As you noticed a join will change the table. A resident load from the table has the same effect. The workaround is to store the table into a qvd directly after creating it, then dropping it and at the end of the script load the table again from the qvd. But don't join it, that will simply disable the input field again. If you need it in one table then first create the joined table, store that table into a qvd and drop it, then define the input field and load the table from the qvd.

Re: Join on table containing input fields - a bug ?

Thu, 18 Apr 2013 19:37:04 GMT

Hi Gysbert,

Thanks for your response. However, which table are you referring to ? I tried the following two approaches but both did not work.

I tried

1. Storing ProductThresholds into qvd and doing a left join on ProductDim directly from ProductThresholds.qvd

INPUTFIELD ProductThreshold;

ProductThresholds:

LOAD * INLINE [

ProductName, ProductThreshold

P1, -0.025

P2, -0.5

];

ProductDim:

LOAD * INLINE [

ProductName

];

STORE ProductThresholds INTO ProductThresholds.qvd;

DROP TABLE ProductThresholds;

LEFT JOIN(ProductDim)

LOAD *

From

ProductThresholds.qvd (qvd);

2. I also tried storing ProductDim after joining with ProductThresholds into qvd, dropping it and loading it back like this -

INPUTFIELD ProductThreshold;

ProductThresholds:

LOAD * INLINE [

ProductName, ProductThreshold

P1, -0.025

P2, -0.5

];

ProductDim:

LOAD * INLINE [

ProductName

];

LEFT JOIN(ProductDim)

LOAD *

Resident

ProductThresholds;

STORE ProductDim INTO ProductDim.qvd;

DROP TABLE ProductDim;

ProductDim:

LOAD *

FROM

ProductDim.qvd(qvd);

Re: Join on table containing input fields - a bug ?

Thu, 18 Apr 2013 19:39:01 GMT

Just a thought.

If you use just this:

INPUTFIELD ProductThreshold;

ProductThresholds:

LOAD * INLINE [

ProductName, ProductThreshold

P1, -0.025

P2, -0.5

];

ProductDim:

LOAD * INLINE [

ProductName

];

You will have two tables but the desired results

Re: Join on table containing input fields - a bug ?

Thu, 18 Apr 2013 19:40:57 GMT

Hi Gysbert,

Thanks for the idea.

As you suggested, the following worked -

ProductThresholds:

LOAD * INLINE [

ProductName, ProductThreshold

P1, -0.025

P2, -0.5

];

ProductDim:

LOAD * INLINE [

ProductName

];

LEFT JOIN(ProductDim)

LOAD

Resident

ProductThresholds;

STORE ProductDim INTO ProductDim.qvd;

DROP TABLE ProductDim;

INPUTFIELD ProductThreshold;

ProductDim:

LOAD

Distinct *

FROM

ProductDim.qvd(qvd);

Re: Join on table containing input fields - a bug ?

Thu, 18 Apr 2013 19:43:14 GMT

Hi Steve,

Thanks for the response. It was just the requirement to create a single table, thats why i was looking for a join.