11 Replies Latest reply: Nov 10, 2016 12:52 PM by Evgenii Senko

# Many to one relationship

Hi,

Need help, this is urgent!

I have to tables.

Have to be relationship between them.

Code1 - AllCodes

Code2 - AllCodes

Code3 - AllCodes

After update if select Amigo will be showed "Store1" according to Code1.

If select "Mall" will be showed Store1 and Store2 according to Code1 and Code3.

Codes:

 Code1 Code2 Code3 Store 11 22 33 Store1 W4 S2 V7 Store2

Info:

 AllCodes Type Square Name 11 Street 100 Amigo W4 Mall 200 Jump 33 Mall 300 Free

• ###### Re: Many to one relationship

Hi,

1-example

TMP:
[
Code1,     Code2,     Code3,     Store
11,  22,  33,  Store1
W4,  S2,  V7,  Store2
]
;

CODES:
LOAD Code1 AS AllCodes, Store Resident TMP;
LOAD Code2 AS AllCodes, Store Resident TMP;
LOAD Code3 AS AllCodes, Store Resident TMP;

Left Join

INFO:
[
AllCodes,Type,Square,Name
11,  Street,    100,Amigo
W4,  Mall, 200,Jump
33,  Mall, 300,Free
]
;

DROP Table TMP;

• ###### Re: Many to one relationship

Hi fer fer,

In real picture I have more then three rows in table.

They are thouthands.

What is code should be in these parts ?

TMP:
[
Code1,     Code2,     Code3,     Store
11,  22,  33,  Store1
W4,  S2,  V7,  Store2
]
;

INFO:
[
AllCodes,Type,Square,Name
11,  Street,    100,Amigo
W4,  Mall, 200,Jump
33,  Mall, 300,Free
]
;

• ###### Re: Many to one relationship

Maybe I shouldn't reply for him, but his example does the same as mine does, converts columns into rows. In that sense, it's the "same" solution. Like my example, it doesn't matter how many rows you have in either table. It should still work fine. And replace any inline load with whatever your real data source is. I suspect that crosstable is a more efficient way to convert the columns into rows, though. That's its whole job. I've done no performance testing, though.

• ###### Re: Many to one relationship

I'd use a crosstable load. See attached and below.

Codes:
CROSSTABLE (Codenum,Code)
Store,Code1,Code2,Code3
Store1,11,22,33
Store2,W4,S2,V7
];

Data:
Code,Type,Square,Name
11,Street,100,Amigo
W4,Mall,200,Jump
33,Mall,300,Free
];

DROP FIELD Codenum;

• ###### Re: Many to one relationship

John,

Thanks for the example.

Please help to understand what should be the code if I have a lot of rows of data in these tables?

I can't enter it manually.

• ###### Re: Many to one relationship

We use inline loads with only a few rows when giving examples because that's a very clear way to show example data, and not have to know whether you're loading using SQL, from a QVD, from an Excel file, or whatever. Replace what we load inline with whatever your actual data source is. Those details are usually beside the point, not really relevant to solving the specific question.

In this case, it doesn't matter how many rows you have in the tables. The crosstable is sensitive to the column names and positions, so you'd have to be careful when loading your real table to have things in the right order. You might even want to split the first table into two tables linked by an ID. But it's hard for me to know without knowing your actual data. And it's hard for me to give you exact script without knowing your data sources as well. But assuming you have a lot more fields in both tables, and you're loading from QVDs, it might look something like this, including my split of your first table into two, only "necessary" if you have additional fields in that first table:

StoreData:
StoreID
,StoreName
,some
,other
,fields
FROM Store.qvd (QVD)
;
StoreCodes:
CROSSTABLE (Codenum,Code)
StoreID
,Code1
,Code2
,Code3
,Code4
,etc. for all codes
FROM Store.qvd (QVD)
;
CodeData:
Code
,Type
,Square
,Name
,Something
,SomethingElse
,AndSoOn
FROM Codes.qvd (QVD)
;

But even that's not what I'd really do, because in the real world I'd be breaking apart the data before even storing in QVDs, not breaking it apart loading from QVDs. But I hope I don't have to expand this example even further to get the idea across. In any case, the important insight is to use crosstable to restructure the code portion of the data from the first table, converting columns into rows. This load is not sensitive to how many rows and columns you have, any more than any other load is sensitive to how many rows and columns you have.

• ###### Re: Many to one relationship

John,

According to your explanation I understood how CrossTable function works.

But my mistake I didn't show all structure of tables.

I was trying to implement this solution for my task but found an issue.

My table includes other columns which I have to use also.

Codes:

 Code1 Code2 Code3 Store SalesQty Margin etc 11 22 33 Store1 1500 1000000 etc1 W4 S2 V7 Store2 2000 200000 etc2

Info:

 AllCodes Type Square Name 11 Street 100 Amigo W4 Mall 200 Jump 33 Mall 300 Free

CrossTable convert all columns in one.

How can I see also such metrics as SalesQty, Margin, etc if use CrossTable relationship?

• ###### Re: Many to one relationship

You can see those metrics if you break it into two tables exactly like I showed above.

StoreData:
Store
,SalesQty
,Margin
,etc
;
StoreCodes:
CROSSTABLE (Codenum,AllCodes)
Store
,Code1
,Code2
,Code3
;
Info:
AllCodes
,Type
,Square
,Name
;

• ###### Re: Many to one relationship

Does "Store" column must have unique values for this kind of relationship?

• ###### Re: Many to one relationship

Yes. If store is not a unique key, you'd probably want to use whatever the unique key to the table actually is. Hard to say, as it throws some of my basic assumptions about your data out the window. What IS the key to your table? What does your data ACTUALLY look like?

• ###### Re: Many to one relationship

That table doesn't have key field.

I found one way to solve it.

Put RowNo() in both tables.

Is it correct mothod?

StoreData:

RowNo()
,Store
,SalesQty
,Margin
,etc
;
StoreCodes:
CROSSTABLE (Codenum,AllCodes)

RowNo()

,Store
,Code1
,Code2
,Code3