Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
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.
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);
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
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);
Hi Steve,
Thanks for the response. It was just the requirement to create a single table, thats why i was looking for a join.