Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have an issue with the load of really large table. It deals with user to customer relationships and is the preparation for the section access.
First of all, I load a smaller table (about 1000 rows) with users who shall be able to see everything.
Then I load a table that has about 1 billion rows with a "where not exists (user_id)" clause,
Table A:
LOAD
USER_ID,
VIEW_ALL_FLAG
FROM
...
;
Table B:
LOAD
USER_ID,
CUSTOMER_ID
FROM
....
Where not exists(USER_ID, USER_ID)
;
The load takes several hours. How can I redesign the load to take shorter time?
Thanks,
Jens
HI
Try like this
MapUserID:
Mapping Load USER_ID, 1 Resident TableA;
Load * where Flag = 1;
Load *, ApplyMap('MapUserID', USER_ID, 0) AS Flag From TableB;
Hi,
that's not the result that I need. Most likely it was not understable without sample data, sorry for that.
So small example:
TableA contains USER_ID 'A', so 'A' shall be allowed to see everything later in the application.
USER_ID VIEW_ALL_FLAG
A Y
The source for TableB contains:
USER_ID CUSTOMER_ID
A 1
A 2
B 1
B 4
B 8
C 3
My final TableB shall contain this:
USER_ID CUSTOMER_ID VIEW_ALL_FLAG
A Y
B 1
B 4
B 8
C 3
So 'A' does not have a direct assignment to a customer as he is a VIEW_ALL user. 'B' and 'C' have direct assignments as they are normal users.
Maybe change
Load * where Flag = 1;
to
Load * where Flag <> 1;
?
Hi
Please Provide a sample data with expected results.
Hi,
please find attached.
Unfortunately you didn't show the sources from your loadings. I assume that at least the bigger table will be loaded from a qvd-file and then it is important to perform an optimized load which could be only done with no real transformings within the load and with a where-clause with exists(WithOnlyOneParameter) - in your case you didn't need the second parameter and could remove them.
If this isn't enough or it isn't a qvd you need to consider to implement incremental methods.
To both suggestions you will find here many examples: Advanced topics for creating a qlik datamodel
- Marcus
Hi
TRy like this
TableA:
LOAD * INLINE [
User, User_key, View_all
A,A, 1
B,B, 1
C,C, 1
D,D, 1
E,E, 1
F,F, 1
G,G, 1
];
MapTableA:
Mapping LOAD User_key, 1 Resident TableA;
TableB:
LOAD * Where Flag = 0;
LOAD *, ApplyMap('MapTableA', User, 0) AS Flag INLINE [
User, Customer
A, 30
A, 44
A, 73
B, 51
B, 73
B, 21
C, 41
C, 100
C, 75
D, 41
D, 37
D, 2
E, 35
E, 7
E, 37
F, 58
F, 40
F, 70
G, 4
G, 15
G, 42
H, 1
H, 14
H, 28
I, 21
I, 61
I, 77
J, 71
J, 83
J, 60
K, 62
K, 39
K, 78
L, 70
L, 42
L, 26
];
Hi Marcus,
you're right, the tables are QVD based. Unfortunately, the big table is a result of a hierarchy command and some inner joins between QVDs, linking from User to Customer with a key field each.
Hi Jens,
like above mentioned try it with only one exists-parameter. Further it seems that you tried to extract the user from a fact-table (or have you really 1 billion customers ?) and that loading from a customer table or from a beforehand reduced fact-table could be have benefits.
- Marcus