topic Re: Scenario: Getting error when combining QVDs using combination of left join and concatenate in QlikView

Scenario: Getting error when combining QVDs using combination of left join and concatenate

ankurakash — Wed, 01 Oct 2014 22:11:56 GMT

Hi,

QVD and Tables:

I have all the QVDs for associated tables that I wanted to use in my application.

I have one fact table (Trans).

I have two additional tables:

a) Sig

b) Payer

The SQl query is:

SELECT COUNT(*),TO_CHAR(TO_DATE(CREATE_DATE),'MON-YYYY')

FROM SIG SA ,PAYER PT

WHERE TRUNC(SA.CREATE_DATE) BETWEEN '01-jan-2014' AND '31-mar-2014'

AND SA.PAYER_NO=PT.PAYER_NO

GROUP BY TO_CHAR(TO_DATE(SA.CREATE_DATE),'MON-YYYY')

What is required?
I have to create a bar chart which would be based on two tables SIG and Payer.

What I have done so far:

Loaded QVD "Sig". Doing left join with QVD "Payer". Creating a new table "SIG_Payer" which would be a combination of QVDs(SIG and Payer). And then concatenating it with fact table "Trans".

The example code is mentioned below:

Sig:

LOAD

PAYER_NO,

UID,

CREATE_DATE,

Month(CREATE_DATE) as Sig_Month,

Year (CREATE_DATE) as Sig_Year,

's' as FLAG

From ../(QVD);

left join (Sig)

Payer:

LOAD

if(CENTRE_NO=1,'A',if(CENTRE_NO=2,'B')) as TaxCentreNo,

PAYER_NO

//CENTRE_NO

From: // (QVD);

Concatenate (Trans)

Payer_Sig:

LOAD PAYER_NO,

UID,

CREATE_DATE,

Month(CREATE_DATE) as Sig_Month,

Year (CREATE_DATE) as Sig_Year,

's' as FLAG,

if(CENTRE_NO=1,'A',if(CENTRE_NO=2,'B')) as TaxCentreNo

Resident

Sig;

Drop table Sig;

Here, Trans(fact table) is having field PAYER_NO and has field CENTRE_NO in similar format as of QVD Payer.

Issue:

When I reload it throws an error (Trans) field "TaxCentreNo" not found. Though this field is available in both fact table and newly created "Sig_Payer" table (I already added fields of "Payer" using left join with "Sig" in "Sig".)

Request your Step by Step Resolution:

Your guidelines in this regard is much appreciated.

Regards..

Re: Scenario: Getting error when combining QVDs using combination of left join and concatenate

Gysbert_Wassenaar — Thu, 02 Oct 2014 08:27:54 GMT

I'd expect an error about CENTRE_NO not being found when creating Payer_Sig since you commented it out in the load for Payer. Other than that make sure to use the exact case-sensitive field names. Qlikview is case-sensitive about field names. Perhaps you made a typo somewhere.

Re: Scenario: Getting error when combining QVDs using combination of left join and concatenate

ankurakash — Thu, 02 Oct 2014 19:44:38 GMT

Hi,

Thanks for your post. It helped me in fixing the issue. I removed the comment for "CENTRE_NO" and additionally created a common key for "date" coming from two different fields from (Trans) and (Sig). With this Bar chart, I need the field "CREATE_DATE" from QVD "Sig".

The data is yet to be verified. Wondering, if I retain both the fields "CENTRE_NO" and "TaxCentreNo"(which is again derived from "CENTRE_NO") for QVD "payer", won't it generate duplicate values while showing report data. Please advise.

Regards..