topic Re: Loop problem (modeling) in QlikView

Loop problem (modeling)

Sun, 29 Jan 2012 21:40:51 GMT

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.

Loop problem (modeling)

Mon, 30 Jan 2012 05:55:46 GMT

Someone?

Loop problem (modeling)

vijay_iitkgp — Mon, 30 Jan 2012 06:04:56 GMT

Hi,

Your question is not very clear. Can you please send us some sample data in xls for all tables u mentioned.

Loop problem (modeling)

rohit214 — Mon, 30 Jan 2012 06:27:26 GMT

avifarkash

For getting correct data you have to make Composite Key

e.g. AreaTypeId &TypeID as #_KEY and using this key connect each other table

else send me your application

thanks

rohit

Re: Loop problem (modeling)

Tue, 31 Jan 2012 19:23:02 GMT

Hi,

Thank you for helping!!!

i have attached a qvw file.

i will explain what is the desired result:

i have 4 tables:

1. log - for each orderID i have the its time, order quantity, and order type

2.hirar - Area -> Site -> Order

3.Min Area - for Order Type id under area i have the minimum amount.

4.Min Site - same as above but for sites.

now i need to present 2 tables:

1. Area Suming: will show area name , all order type id of the area, area minimum amount, sum of order qty.

2. same as above but for site.

i hope it was clear now, if not please ask me...

Thanks a lot.

Avi

Loop problem (modeling)

Wed, 01 Feb 2012 07:43:21 GMT

Someone?

Loop problem (modeling)

hic — Wed, 01 Feb 2012 08:49:56 GMT

If you want a data model with several tables, you need to make a composite key just like Rohit suggests. The reason for this is that your attributes in MinArea and MinSite touch both the dimensions defined by OrderID and OrderTypeID.

However, there is a simpler way: Start by loading the transaction table (log), then join the other tables onto this table. First the Hierarchy table, then the other tables.

LOAD * FROM log.qvd (qvd);
Left join LOAD * FROM hirar.qvd (qvd);
Left join LOAD * FROM MinArea.qvd (qvd);
Left join LOAD * FROM MinSite.qvd (qvd);

This way you will have only one table – a table that has a few extra columns but has the same length as the log file. So it will still be a good solution.

Re: Loop problem (modeling)

rohit214 — Wed, 01 Feb 2012 09:11:16 GMT

Hi Avi

I dont have your data so i cant reload to chek it,

but try this,

1)in Min Area table alias OrderTypeID to Order_Type_ID

2) In Log table alias OrderTypeID to OrderType_ID

and let me know its working or not

thanks

rohit

Re: Loop problem (modeling)

Wed, 01 Feb 2012 09:15:47 GMT

Thank you Henric ,

i have thought about your sulotion the only problem is that if there is for Site or Area a minimum and there is no log (OrderID under Site or Area) i need to show the minimum amount and 0 in sum of order qty.

Re: Loop problem (modeling)

Wed, 01 Feb 2012 09:17:55 GMT

hi rohit and thank you for helping,

i didnt understand you sulotion .

i have attached a qvd files.

i hope its ok.

avi

Re: Loop problem (modeling)

rohit214 — Wed, 01 Feb 2012 09:30:07 GMT

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

Re: Loop problem (modeling)

Wed, 01 Feb 2012 09:37:32 GMT

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

Re: Loop problem (modeling)

rohit214 — Wed, 01 Feb 2012 09:51:16 GMT

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

Re: Loop problem (modeling)

Wed, 01 Feb 2012 10:01:06 GMT

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

Re: Loop problem (modeling)

rohit214 — Wed, 01 Feb 2012 11:07:08 GMT

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

Re: Loop problem (modeling)

Wed, 01 Feb 2012 11:25:00 GMT

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

Loop problem (modeling)

Thu, 02 Feb 2012 09:55:10 GMT

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

Re: Loop problem (modeling)

Wed, 07 Aug 2013 09:28:58 GMT

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.