topic Re: How to resolve this loops in QlikView

How to resolve this loops

Mon, 10 Nov 2014 08:19:09 GMT

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

Re: How to resolve this loops

alexandros17 — Mon, 10 Nov 2014 08:27:09 GMT

left join sales with invoice line:

invoice line

left join

sales

let me know

Re: How to resolve this loops

its_anandrjs — Mon, 10 Nov 2014 08:27:38 GMT

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

Re: How to resolve this loops

Mon, 10 Nov 2014 08:43:43 GMT

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);

Re: How to resolve this loops

alexandros17 — Mon, 10 Nov 2014 08:53:08 GMT

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)

Re: How to resolve this loops

its_anandrjs — Mon, 10 Nov 2014 09:03:26 GMT

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

Re: How to resolve this loops

Mon, 10 Nov 2014 09:18:59 GMT

Still the same and appeared synthetic keys.

Re: How to resolve this loops

Mon, 10 Nov 2014 09:20:59 GMT

hi,

comment sl-id from region_sline and service_id from invoice_line.

cheers

kunal bhattacharjee

Re: How to resolve this loops

Tue, 11 Nov 2014 03:01:17 GMT

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);

Re: How to resolve this loops

Tue, 11 Nov 2014 05:07:07 GMT

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

Re: How to resolve this loops

Tue, 11 Nov 2014 05:13:38 GMT

Thank you Kunal.