Problem because of distinct clauses while storing table in QVD

[deepakqlikview_123]

Hi All,

I am creating transformed QVD for the original tables to follow 3 tier architecture.

But the problem is that whenever if there is any distinct clause and in the main table and if I can create QVD,

after using the created QVD for the end application results are not match.

Could you please suggest what are the possible ways to avoid this issue.

Thanks

[jyothish8807]

Hi  Deepak,

I am not sure if i have understood your query clearly,but see below approach as per my understanding:

Table: // Your master table coming from source- never use distinct for this.

A:

Load

Name,

Department,

Salary

from <your source>;

Noconcatenate

B: //Now if you want to reduce the rows try like this:

Load

Name,

Department,

Sum(Salary) as Salary

resident A

group by Name,Department;

Store B into B.qvd;

Drop table A;

Hope this helps.

[jyothish8807]

Hi  Deepak,

I am not sure if i have understood your query clearly,but see below approach as per my understanding:

Table:    // Your master table coming from source- never use distinct for this.

A:

Load

Name,

Department,

Salary

from <your source>;

Noconcatenate

B: //Now if you want to reduce the rows try like this:

Load

Name,

Department,

Sum(Salary) as Salary

resident A

group by Name,Department;

Store B into B.qvd;

Drop table A;

Hope this helps.

[deepakqlikview_123]

Hi KC,

PFB code,

[Order Delivery Temp]:
LOAD DISTINCT
[Sales Document Item Key],
[Planned Goods Issue Date],
[Delivery Document Creation Date],
[Actual Goods Issue Date],
Round (Sum ([Delivered Quantity]), 0.01) as [Delivered Quantity Punctuality],
If (Sum ([Delivered Quantity]) > 0, 1, 0) as [Delivered flag]
FROM
[SalesOrderDeliverySSL.qvd]
(qvd)
// Do not retrieve deleted lines
WHERE [Order Schedule Line Delivery Deleted] <> 'X'
AND WildMatch ('$(vItemCategoryPunctualityLimitation)', '*' & ApplyMap ('Item Category Map', [Sales Document Item Key]) & '*') = 0
AND Len ([Sales Document Item Key]) > 0
// AND NOT EXISTS ([%Sales Document TECO Complete Item], [Sales Document Item Key])
GROUP BY [Sales Document Item Key],
[Planned Goods Issue Date],
[Delivery Document Creation Date],
[Actual Goods Issue Date];


DIRECTORY $(VOPTQVD);

STORE [Order Delivery Temp] into [Order Delivery Temp].qvd(QVD);
drop table [Order Delivery Temp];

If I store the above result in QVD many times result in transformed QVD not matched with the original result.

Can anyone suggest me the  best way to perform the above action.

Thanks

[olivierrobin]

hello,

as you are using sum() ans group by , you don't need to use distinct.

I thionk you want 1 row with the sum of columns for each combination of key fields