topic Re: Many to one relationship in QlikView

Many to one relationship

Yevhenii_Senko — Wed, 09 Nov 2016 18:49:58 GMT

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!

Re: Many to one relationship

el_aprendiz111 — Wed, 09 Nov 2016 22:20:32 GMT

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;

Re: Many to one relationship

Yevhenii_Senko — Wed, 09 Nov 2016 22:41:51 GMT

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

Re: Many to one relationship

johnw — Wed, 09 Nov 2016 23:22:54 GMT

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;

Re: Many to one relationship

Yevhenii_Senko — Wed, 09 Nov 2016 23:43:41 GMT

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.

Re: Many to one relationship

johnw — Thu, 10 Nov 2016 00:38:05 GMT

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.

Re: Many to one relationship

johnw — Thu, 10 Nov 2016 00:43:28 GMT

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

Yevhenii_Senko — Thu, 10 Nov 2016 08:30:28 GMT

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

johnw — Thu, 10 Nov 2016 16:47:08 GMT

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
;

Re: Many to one relationship

Yevhenii_Senko — Thu, 10 Nov 2016 17:08:28 GMT

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

Re: Many to one relationship

johnw — Thu, 10 Nov 2016 17:49:22 GMT

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

Yevhenii_Senko — Thu, 10 Nov 2016 17:52:43 GMT

That table doesn't have key field.

I found one way to solve it.

Put RowNo() in both tables.

Is it correct mothod?

StoreData:
LOAD

RowNo()
,Store
,SalesQty
,Margin
,etc
FROM your codes data source
;
StoreCodes:
CROSSTABLE (Codenum,AllCodes)
LOAD

RowNo()

,Store
,Code1
,Code2
,Code3
FROM your codes data source
;
Info:
LOAD
AllCodes
,Type
,Square
,Name
FROM your info data source
;