Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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
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
Hello Kaushik Solanki,
Awesome, it works fine what I requested.
Regards
Chriss