Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
deepaktibhe
Valued Contributor

Problem because of distinct clauses while storing table in QVD

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

4 Replies
jyothish8807
Honored Contributor II

Re: Problem because of distinct clauses while storing table in QVD

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.

Best Regards,
KC
jyothish8807
Honored Contributor II

Re: Problem because of distinct clauses while storing table in QVD

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.

Best Regards,
KC
deepaktibhe
Valued Contributor

Re: Problem because of distinct clauses while storing table in QVD

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
Valued Contributor II

Re: Problem because of distinct clauses while storing table in QVD

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