Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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.
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..