Need help in join two qvd's

[#1]

Hello Friends,

As I have two Qvd's like a Fact.Qvd and Customer.qvd.

1. I would like to join these two qvd's and not using of sythentic key.

2 For this two qvd's the uniqe field is for CUSTOMER.QVD is Customer and [Customer Number], 

     and for Fact.qvd is Cust and Cust_Num.

3. The requirement is joining of these two qvd as I need to get the information of [Customer County]

CUSTOMER:

LOAD Customer,

     [Customer Number],

     [Customer Country],

     [Customer City],

     [Customer Zip],

     [CUSTOMER ADDRESS]

FROM

E:\Data\cUSOTMER.qvd

(qvd);

Fact Table:

Weight,

Number,

Amount,

Cust_Num,

Cust,

Inv_Date,

Ship_Date,

......

FROM

E:\Data\Fact.qvd

(qvd);

Please help me how to do this.

Regards

Chriss

[kaushiknsolanki]

Hi,

     Create a key field to link your two Qvds.

     Like shown below.

CUSTOMER:

LOAD Customer,

     [Customer Number],

     Customer&'-'& [Customer Number] as KEY,

     [Customer Country],

     [Customer City],

     [Customer Zip],

     [CUSTOMER ADDRESS]

FROM

E:\Data\cUSOTMER.qvd

(qvd);

Fact Table:

Load

Weight,

Number,

Amount,

//Cust_Num,

//Cust,

Cust&'-'&Cust_Num as KEY,

Inv_Date,

Ship_Date,

......

FROM

E:\Data\Fact.qvd

(qvd);

Here you need to comment the Cust_Num and Cust field, cause you are taking it from Master.

Regards,

Kaushik Solanki

[#3]

Hello Kaushik Solanki,

How are you, I can not see you long back.

Thanks for your reply, the altimate aim is when I create a filter(Multibox for Customer), its showing all the records of Master Customer table, but I would like to show the only Customer records of data which is in the Fact table.

Please help me how to do this.

Hope I am clear.

Regards

Chriss

[kaushiknsolanki]

Hi,

     Thanks for looking my help.

     All you have to do is create a flag in Fact Table.

     Something like this.

     '1' as Flag

     and use this flag in set analysis as shown below.

     sum({<Flag = {"1"}>} Sales)

Regards,

Kaushik Solanki

[#5]

Hello Kaushik Solanki,

Thanks again, the above said "Flag" is not quite clear to me, as I am in the basic level, could you please give any

worked example.

It will be help full to proceed futher.

Regards

Chriss

[kaushiknsolanki]

Hi,

Use below script.

CUSTOMER:

LOAD Customer,

     [Customer Number],

     Customer&'-'& [Customer Number] as KEY,

     [Customer Country],

     [Customer City],

     [Customer Zip],

     [CUSTOMER ADDRESS]

FROM

E:\Data\cUSOTMER.qvd

(qvd);

Fact Table:

Load

Weight,

Number,

Amount,

//Cust_Num,

//Cust,

'1' as Flag

Cust&'-'&Cust_Num as KEY,

Inv_Date,

Ship_Date,

......

FROM

E:\Data\Fact.qvd

(qvd);

Now you can use set expression as shown below in your chart.

sum({<Flag = {"1"}>} Sales)

Regards,

Kaushik Solanki

[kaushiknsolanki · Accepted solution]

If you want to have values only from fact table then you can do something like this.

Fact Table:

Load

Weight,

Number,

Amount,

//Cust_Num,

//Cust,

'1' as Flag

Cust&'-'&Cust_Num as KEY,

Inv_Date,

Ship_Date,

......

FROM

E:\Data\Fact.qvd

(qvd);

CUSTOMER:

LOAD Customer,

     [Customer Number],

     Customer&'-'& [Customer Number] as KEY,

     [Customer Country],

     [Customer City],

     [Customer Zip],

     [CUSTOMER ADDRESS]

FROM

E:\Data\cUSOTMER.qvd

(qvd) where exists(KEY,Customer&'-'& [Customer Number]);

Regards,

Kaushik Solanki

[#8]

Hello Kaushik Solanki,

Awesome, it works fine what I requested.

Regards

Chriss