Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Thanks in advance!
I'd use a crosstable load. See attached and below.
Codes:
CROSSTABLE (Codenum,Code)
LOAD * INLINE [
Store,Code1,Code2,Code3
Store1,11,22,33
Store2,W4,S2,V7
];
Data:
LOAD * INLINE [
Code,Type,Square,Name
11,Street,100,Amigo
W4,Mall,200,Jump
33,Mall,300,Free
];
DROP FIELD Codenum;
Hi,
1-example
TMP:
LOAD * Inline
[
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:
LOAD * Inline
[
AllCodes,Type,Square,Name
11, Street, 100,Amigo
W4, Mall, 200,Jump
33, Mall, 300,Free
];
DROP Table TMP;
Hi fer fer,
Thank you for reply.
In real picture I have more then three rows in table.
They are thouthands.
What is code should be in these parts ?
TMP:
LOAD * Inline
[
Code1, Code2, Code3, Store
11, 22, 33, Store1
W4, S2, V7, Store2
];
INFO:
LOAD * Inline
[
AllCodes,Type,Square,Name
11, Street, 100,Amigo
W4, Mall, 200,Jump
33, Mall, 300,Free
];
I'd use a crosstable load. See attached and below.
Codes:
CROSSTABLE (Codenum,Code)
LOAD * INLINE [
Store,Code1,Code2,Code3
Store1,11,22,33
Store2,W4,S2,V7
];
Data:
LOAD * INLINE [
Code,Type,Square,Name
11,Street,100,Amigo
W4,Mall,200,Jump
33,Mall,300,Free
];
DROP FIELD Codenum;
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.
It shoud be downloaded by script.
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:
LOAD
StoreID
,StoreName
,some
,other
,fields
FROM Store.qvd (QVD)
;
StoreCodes:
CROSSTABLE (Codenum,Code)
LOAD
StoreID
,Code1
,Code2
,Code3
,Code4
,etc. for all codes
FROM Store.qvd (QVD)
;
CodeData:
LOAD
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.
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.
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?
You can see those metrics if you break it into two tables exactly like I showed above.
StoreData:
LOAD
Store
,SalesQty
,Margin
,etc
FROM your codes data source
;
StoreCodes:
CROSSTABLE (Codenum,AllCodes)
LOAD
Store
,Code1
,Code2
,Code3
FROM your codes data source
;
Info:
LOAD
AllCodes
,Type
,Square
,Name
FROM your info data source
;
Does "Store" column must have unique values for this kind of relationship?