How to resolve this loops

[#1]

How to resolve the loops?

Thanks in advance.

Age_group:
LOAD `age_group_id`,
    `age_min`,
    `age_max`,
    `age_range`;
SQL SELECT *
FROM `C:\Island Marketing\club`.`Age_group`;

City:
LOAD `city_id`,
    city,
    `region_id`;
SQL SELECT *
FROM `C:\Island Marketing\club`.City;

Country:
LOAD `country_id`,
    country;
SQL SELECT *
FROM `C:\Island Marketing\club`.Country;

LOAD `cust_id`,
    `first_name`,
    `last_name`,
    age,
    `phone_number`,
    address,
    `city_id`,
    `sales_id`,
    `sponsor_id`;
SQL SELECT *
FROM `C:\Island Marketing\club`.Customer;

Customer:
LOAD `inv_id`,
    `service_id`,
    days,
    `nb_guests`;

Invoice_Line:
SQL SELECT *
FROM `C:\Island Marketing\club`.`Invoice_Line`;

LOAD `region_id`,
    region,
    `country_id`;
SQL SELECT *
FROM `C:\Island Marketing\club`.Region;

Region:
LOAD `sl_id`,
    `region_id`,
    `sales_revenue`;
Region_SLine:
SQL SELECT *
FROM `C:\Island Marketing\club`.`Region_SLine`;

LOAD `res_id`,
    `service_id`,
    `res_days`,
    `future_guests`;
Reservation_Line:
SQL SELECT *
FROM `C:\Island Marketing\club`.`Reservation_Line`;

LOAD `res_id`,
    `cust_id`,
    `res_date`;
Reservations:
SQL SELECT *
FROM `C:\Island Marketing\club`.Reservations;

Resort:
LOAD `resort_id`,
    resort,
    `country_id`;
SQL SELECT *
FROM `C:\Island Marketing\club`.Resort;

Sales:
LOAD `inv_id`,
    `cust_id`,
    `invoice_date`;
SQL SELECT *
FROM `C:\Island Marketing\club`.Sales;

Sales_Person:
LOAD `sales_id`,
    `sales_person`;
SQL SELECT *
FROM `C:\Island Marketing\club`.`Sales_Person`;

Service:
LOAD `service_id`,
    service,
    `sl_id`,
    price;
SQL SELECT *
FROM `C:\Island Marketing\club`.Service;

Service_Line:
LOAD `sl_id`,
    `service_line`,
    `resort_id`;
SQL SELECT *
FROM `C:\Island Marketing\club`.`Service_Line`;
//-------- End Multiple Select Statements ------

[alexandros17]

left join sales with invoice line:

invoice line

left join

sales

let me know

[its_anandrjs]

Hi,

You can resolve this issues by solving Synthetic keys and circular loops problem

Synthetic Keys

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

Circular Loop

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/25/circular-references

Regards

Anand

[#4]

Still the loop is there.

Please advise.

Age_group:
LOAD age_group_id,
     age_min,
     age_max,
     age_range
FROM
C:\Island Marketing\Age_group.QVD
(qvd);

City:

LOAD city_id,
     city,
     region_id
FROM
C:\Island Marketing\City.QVD
(qvd);

Country:
LOAD country_id,
     country
FROM
C:\Island Marketing\Country.QVD
(qvd);

Customer:
LOAD cust_id,
     first_name,
     last_name,
     age,
     phone_number,
     address,
     city_id,
     sales_id,
     sponsor_id
FROM
C:\Island Marketing\Customer.QVD
(qvd);

Region:

LOAD region_id,
     region,
     country_id
FROM
C:\Island Marketing\Region.QVD
(qvd);

Region_SLine:
LOAD sl_id,
     region_id,
     sales_revenue
FROM
C:\Island Marketing\Region_SLine.QVD
(qvd);

Reservation_Line:
LOAD res_id,
     service_id,
     res_days,
     future_guests
FROM
C:\Island Marketing\Reservation_Line.QVD
(qvd);

Reservations:

LOAD res_id,
     cust_id,
     res_date
FROM
C:\Island Marketing\Reservations.QVD
(qvd);

Resort:

LOAD resort_id,
     resort,
     country_id
FROM
C:\Island Marketing\Resort.QVD
(qvd);

Invoice_Line:

LOAD inv_id,
     service_id,
     days,
     nb_guests
FROM
C:\Island Marketing\Invoice_Line.QVD
(qvd);

LEFT JOIN
Sales:

LOAD inv_id,
     cust_id,
     invoice_date
FROM
C:\Island Marketing\Sales.QVD
(qvd);

Sales_Person:

LOAD sales_id,
     sales_person
FROM
C:\Island Marketing\Sales_Person.QVD
(qvd);

Service:

LOAD service_id,
     service,
     sl_id,
     price
FROM
C:\Island Marketing\Service.QVD
(qvd);

Service_Line:

LOAD sl_id,
     service_line,
     resort_id
FROM
C:\Island Marketing\Service_Line.QVD
(qvd);

[alexandros17 · Accepted solution]

you're right, I did not see reservation ...

so do the same (left join)

reservation

left join

reservatio ... (I do not see the whole name in diagram)

[its_anandrjs]

Hi,

If you are using multiple keys in the table then by unique key join the table or you can make composite keys

Eg:-

Load

Field1&'_'&Field2 as Key

..

..

..

From Source;

Note :- Also load table one by one by using Exit script;  at the end of load script.

Regards

Anand

[#7]

Still the same and appeared synthetic keys.

[#8]

hi,

comment sl-id from region_sline and service_id from invoice_line.

cheers

kunal bhattacharjee

[#9]

I used Loosen function by removing joins.

Now there is no loops. Is this good practice?

Invoice_Line:

LOAD inv_id,
    service_id,
     days,
     nb_guests
FROM
C:\Island Marketing\Invoice_Line.QVD
(qvd);
Loosen table Invoice_Line;

//LEFT JOIN
Sales:

LOAD inv_id,
     cust_id,
     invoice_date
FROM
C:\Island Marketing\\Sales.QVD
(qvd);

[#10]

hi,

I haven't used 'loosen table' command but, from what I've heard it is not a good practice. i go for rather traditional approach.

cheers,

kunal bhattacharjee