More records are showing after the QV association

[Anonymous]

//1Table 4996 records

Master_Calendar_Temp:

LOAD

        IF((YEAR(calendar_date) >2011 OR ( MONTH(calendar_date)>9 AND YEAR(calendar_date) =2011)),

            (DATE(FLOOR(TIMESTAMP(_product_asset_as_of_date,'YYYY-MM-DD hh:mm:ss')))),

                  (DATE(FLOOR(TIMESTAMP(product_asset_month_end_date,'YYYY-MM-DD hh:mm:ss')))))

              AS _product_asset_as_of_date_k,

              *

RESIDENT Master_Calendar  where DATE(FLOOR(TIMESTAMP(calendar_date))) <=date(today());

//2Table 700000records

Sales_Temp:

LOAD

(DATE(FLOOR(TIMESTAMP(TNA_DATE,'YYYY-MM-DD hh:mm:ss'))))  AS _product_asset_as_of_date_k,

   Sales_Amount

FROM $(vQVDLevel1ProductAssetsPathName)Sales*.QVD (qvd);

After exectuing the load script QV is giving 3 Million records.  It should have ideally 700000 records but in the final table they are coming around 2 million

Please help me understanding this behaviour.

Thanks

Dasu.G

[#2]

Hi,

Use Right Join

Hope it helps you.

Best of luck

[Anonymous]

We want all calendar dates from master calendar  that is the reason we have used left join still the record count is going 3 millions..

LEFT JOIN (Master_Calendar_Temp)

LOAD *  RESIDENT Product_Assets_Temp;

[jagan]

Hi,

In your script you didn't joined the tables, so there is no chance for records duplication. 

Since you are using Sales*.QVD (qvd) in the script, so it loads all the QVDs starts with Sales, check whether how many qvds are there which name starts with Sales.

Hope this helps you.

Regards,

Jagan.

[Anonymous]

You are right before joining it was good and giving 4996 and 700000 . But the time when I join these two table using outer join or left join it is giving 3 mil records. Am i not supposed to join these tables?

LEFT JOIN (Master_Calendar_Temp)

LOAD *  RESIDENT Product_Assets_Temp;

[jagan]

Hi,

If there are many to many or one to many mappings then the records will be duplicated, better don't join the tables, qlikview manages the relation between the tables.

Hope this helps you.

Regards,

Jagan.

[Anonymous]

We have to join these two views since we are using some derived columns from both the the views. Eventhoug number goes high but caliculations are not doubled and application looks good in functionality point but applicaiton size became so high. Any thoughts?

[jagan]

Hi,

If there are many to many mapping or one to many mapping between tables, definitely there records are going to be increased, if records are increased the size of the application increases.  So, join tables with necessary columns instead of joining with all the columns, so that the size would be reduced a bit.

Regards,

Jagan.

[kaushiknsolanki]

Hi,

     I agree with Jagan and would like to give an exmaple of the same.

     Consider you have table A and table B.

     Table A:

     Field1, Field2

     A,10

     A,20

     B,30

     C,40

     Table B:

     Field1, Field3

     A, 100

     A,200

     B,300

     C,400

     So now if you join then then the result will be like this.

     Field1, Field2, Field3.

     A,10,100

     A,10,200

     A,20,100

     A,20,200

     B,30,300

     C,40,400

     As you can see the result table contains 6 records where as the base table have only 4 records.

     For more clarification read the document attached.

Regards,

Kaushik Solanki