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

[ankurakash]

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..

[Gysbert_Wassenaar]

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.

[ankurakash]

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..