Loop problem (modeling)

[#1]

Hello all,

i need an advice to solve this problem:

i have 4 table:

for each area (site) i need to show its AreaTypeId(SiteTypeId) AreaQty(SiteQty) and its OrderQty, something like that:

but the problem is  connection between AreaTypeId  and TypeID because for each AreaTypeId  i need to connect it to PartID and sum the OrderQty.

by connecting the fields i get a loop.

any idea how to do that?

thank you all.

[rohit214]

Hi

Try this script

Directory;

LOAD AreaID,

     AreaName,

     OrderID,

     OrderName,

     SiteID,

     SiteName

FROM

hirar.qvd

(qvd);

LOAD OrderID,

     OrdersQty,

     Time,

     OrderTypeID as OrderType_ID

FROM

log.qvd

(qvd);

LOAD AreaID,

     AreaQty,

     OrderTypeID as Order_Type_ID

FROM

MinArea.qvd

(qvd);

LOAD SiteID,

     SiteQty,

     OrderTypeID

FROM

MinSite.qvd

(qvd);

thanks

rohit

[#3]

i try your script but when i creat a straight table to sum area order i cant connect between OrderType_ID (from hirarchy and log) and Order_Type_ID (from MinArea).

thank you very much

[rohit214]

Hi Avi

I m not able to get  you properly.can you give the format or table what you want,??

make a table and put that value as per your requirment and let me know so i can understand better way.

thanks

rohit

[#5]

i attached a qvw with a table and my requirments.

Area Name	Order Type ID	Min Area Qty	Order Sum
Area_1	B	20	11765
Area_1	BE	100	0
Area_1	C	2000	1200
Area_1	S	100	0
Area_2	B	333	190
Area_2	C	100000	0
Area_3	B	300001	1700

i need to create a table as above.

thanks a lot

[rohit214]

HI avi

i attached a  modified application

please let me know its working or not and one more thing  still your requirement is not clear  for me

if i am wrong thenplease explain me again

thanks

rohit

[#7]

no it still not the what i need, i will explain:

there is an hirarchy: Area -> Site -> Orders

for Site and Area there is a minimum qty.

i need to create a table that show for each Area  all its order type id and sum of all orders that under this area and have the same Order Type ID , if there is no order with  Order Type ID (from MinArea) i need to show order type minimum and sum = 0 .

the final table should be like this:

Area Name	Order Type ID	Min Area Qty	Order Sum
Area_1	B	20	11765
Area_1	BE	100	0
Area_1	C	2000	1200
Area_1	S	100	0
Area_2	B	333	190
Area_2	C	100000	0
Area_3	B	300001	1700

in Area_1 id is in MinArea table and has the order type id B,BE,C,S with its minimum value.

now in log table there is order id 4 and 5 both of them under Area_1,

order id = 4 and 100 have the order type id B and the sum of them is 11765.

order id = 5 has order type id C and order sum is 1200,

and for all the other order type id there is no log with order id under Area_1 with order type id BE or S then the sum will be 0.

i hope it was clear now.

i much appreciate your help.

avi

[#8]

Hello:

I am not sure if this can help you in any way but have you consider using Left Join statment?

MinArea:

Left Join (Hirar)
LOAD  AreaID,

          AreaQTY,

          OrderTypeID

From

MinArea.qvd

(qvd);

[#9]

QlikView has no built-in support for avoiding loop excpet the Loosen tables - that is why the modeling must take into account the creation of composite non-organic keys.