Showing posts with label nested. Show all posts
Showing posts with label nested. Show all posts

Monday, March 26, 2012

I'm having issues with nested table...

Hey gang,

I'm having some issues with nested table. This is my setup. [ProductTable] is the case table, and [CustomersTable] is a nested table. I'm trying to organize my algorithms around products.

[ProductTable]<[CustomersTable]

[ProductTable] table only has product ID, and it is key.

[CustomersTable] table has variety of customer attributes (productID, customerID, location, demographics...) and CustomerRevenue is predict_only. ProductName is the key for the nested table.

I keep getting this error when I'm processing the mining models (Logical Regression and Neural Net).

Error (Data mining): In mining model, Estimate Neural Net, the algorithm does not allow table column as predictable.

Error (Data mining): Error validating attribute for the 'Estimate Neural Net' mining model.

When using Decision Tree, it processes OK, but the result is totally wrong. The model is empty.

Any ideas?

-Young K.

P.S. I'm trying to great a single model for multiple products. This is a label saving device that I'm trying. If this doens't work, I'll have to create a model for each product.

Just an idea... it might help to get all the columns needed in a single view in the database insted of using nested tables.

It's not the actual solution to your problem but it might be a workaround
|||

I thought of that, and that lead me to my original question... How good are the estimation (or regression) type analysis if I build a single model for multiple products? For example, if I have data for customers buying cars, motorcycles and boats, should I put them all in one model ? Or should I build 3 different models for 3 seperate products?

Can I build a single model to predict who will buy a car and/or boats and/or motorcycle and/or boat? Should I build a seperate model for car, motorcycle and boat? Is there a difference in accuracy?

I assumed that I needed to build 3 models for 3 products. And I used nested table to create a single model for multiple products. With nested tables, I can clearly seperate data between different products.

Any thoughts?

-Young K.

|||

You can solve the problem you want to, but you are misusing nested tables.

The data you are analyzing is defined by your case table. The nested table simply describes attributes of your case. In your case you have "Products" as the case and "Customers" as an attribute of "Product". The key of the nested table indicates in individual attribute or set of attributes. For example, you will have an attribute "Customer 3's Gender". Of course, customer 3's gender is unlikely to change for each product in their basket.

Another way of thinking about the problem is that your case identifier indicates what is anonymous or "unimportant" about your model. You are trying to spot trends in product purchasing behavior across customers. An individual customer is anonymous or "unimportant", the information about the customer and the products they buy are important.

You could have customers as the case table and have a nested table of products that contains the product name and product revenue from that customer. You would need to make both the table and the product revenue "Predict Only" to ensure that the predictions are not influenced by other product revenues or the existence of other products. If you use Decision Trees in this case, you will get a tree for each product revenue based on customer demographics.

To get a prediction of a product revenure given customer info you would use a query something like this:

SELECT (SELECT Predict(Revenue) FROM Products WHERE [Product Name]='Car') as t FROM ...

|||

Thank you, Jamie. That helped a lot.

-Young K

sql

I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5
Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>
|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I
> I
>

I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
>> so if I did combin(35,3) the result would be 6545 and the combinations
>> are
>> listed below. I know how to do it by creating three nested loops in c#
>> but
> I
>> think there's a way to do it in sql by using a cross join or self join
>> but
> I
>> don't see it.
>> Thanks
>>
>> 1 2 3
>> 1 2 4
>> 1 2 5
>> 1 2 6
>> 1 2 7
>> 1 2 8
>> 1 2 9
>> 1 2 10
>> 1 2 11
>> 1 2 12
>> 1 2 13
>> 1 2 14
>> 1 2 15
>> 1 2 16
>> 1 2 17
>> 1 2 18
>> 1 2 19
>> 1 2 20
>> 1 2 21
>> 1 2 22
>> 1 2 23
>> 1 2 24
>> 1 2 25
>> 1 2 26
>> 1 2 27
>> 1 2 28
>> 1 2 29
>> 1 2 30
>> 1 2 31
>> 1 2 32
>> 1 2 33
>> 1 2 34
>> 1 2 35
>> 1 3 4
>> 1 3 5
>> 1 3 6
>> 1 3 7
>> 1 3 8
>> 1 3 9
>> 1 3 10
>> 1 3 11
>> 1 3 12
>> 1 3 13
>> 1 3 14
>> 1 3 15
>> 1 3 16
>> 1 3 17
>> 1 3 18
>> 1 3 19
>> 1 3 20
>> 1 3 21
>> 1 3 22
>> 1 3 23
>> 1 3 24
>> 1 3 25
>> 1 3 26
>> 1 3 27
>> 1 3 28
>> 1 3 29
>> 1 3 30
>> 1 3 31
>> 1 3 32
>> 1 3 33
>> 1 3 34
>> 1 3 35
>> 1 4 5
>>
>sql

I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I
> I
>