Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Yevhenii_Senko
Contributor III
Contributor III

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: 

Code1Code2Code3Store
112233Store1
W4S2V7Store2

Info:

AllCodesTypeSquareName
11Street100Amigo
W4Mall200Jump
33Mall300Free

Thanks in advance!

1 Solution

Accepted Solutions
johnw
MVP
MVP

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;

Capture.PNG

Capture2.PNG

View solution in original post

11 Replies
el_aprendiz111
Specialist
Specialist

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;

Yevhenii_Senko
Contributor III
Contributor III
Author

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

johnw
MVP
MVP

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;

Capture.PNG

Capture2.PNG

View solution in original post

Yevhenii_Senko
Contributor III
Contributor III
Author

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.

johnw
MVP
MVP

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.

johnw
MVP
MVP

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.

Yevhenii_Senko
Contributor III
Contributor III
Author

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: 

Code1Code2Code3StoreSalesQtyMarginetc
112233Store115001000000etc1
W4S2V7Store22000200000etc2

Info:

AllCodesTypeSquareName
11Street100Amigo
W4Mall200Jump
33Mall300Free

CrossTable convert all columns in one.

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

johnw
MVP
MVP

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
;

Yevhenii_Senko
Contributor III
Contributor III
Author

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