topic Re: Need help in join two qvd's in QlikView

Need help in join two qvd's

Wed, 06 Mar 2013 06:09:04 GMT

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

Re: Need help in join two qvd's

kaushiknsolanki — Wed, 06 Mar 2013 06:13:22 GMT

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

Re: Need help in join two qvd's

Wed, 06 Mar 2013 06:56:37 GMT

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

Re: Need help in join two qvd's

kaushiknsolanki — Wed, 06 Mar 2013 07:00:16 GMT

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

Re: Need help in join two qvd's

Wed, 06 Mar 2013 07:18:14 GMT

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

Re: Need help in join two qvd's

kaushiknsolanki — Wed, 06 Mar 2013 08:30:15 GMT

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

Re: Need help in join two qvd's

kaushiknsolanki — Wed, 06 Mar 2013 08:31:47 GMT

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

Re: Need help in join two qvd's

Wed, 06 Mar 2013 09:22:39 GMT

Hello Kaushik Solanki,

Awesome, it works fine what I requested.

Regards

Chriss