Join on table containing input fields - a bug ?

[#1]

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

P1

P2

P3

];

LEFT JOIN(ProductDim)

LOAD

*

Resident

ProductThresholds;

DROP TABLE ProductThresholds;

[Gysbert_Wassenaar · Accepted solution]

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.

[#3]

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

P1

P2

P3

];

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

P1

P2

P3

];

LEFT JOIN(ProductDim)

LOAD *

Resident

ProductThresholds;

STORE ProductDim INTO ProductDim.qvd;

DROP TABLE ProductDim;

ProductDim:

LOAD *

FROM

ProductDim.qvd(qvd);

[#4]

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

P1

P2

P3

];

You will have two tables but the desired results

[#5]

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

P1

P2

P3

];

LEFT JOIN(ProductDim)

LOAD

*

Resident

ProductThresholds;

STORE ProductDim INTO ProductDim.qvd;

DROP TABLE ProductDim;

INPUTFIELD ProductThreshold;

ProductDim:

LOAD

Distinct *

FROM

ProductDim.qvd(qvd);

[#6]

Hi Steve,

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