Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello QlikView users..
i have 2 table that need to filter and create a relation to get the data.
1. branch
Table : branch
fields are : code, branch
code format is : 12345678
where 123 is city code and 45678 is branch code
2. main
table : main
fields are : code, desc, value
code format is : 123456789
where 123 is some policy code, 45678 is branch code, and 9 is a code that un-use
but if i use load script like below :
LOAD MID(code,4,5) AS branch_code, branch as branch_name, code |
FROM
[..\QlikView Data\QVD\M_BRANCH.qvd]
(qvd);
FROM [..\QlikView Data\QVD\M_BRANCH.qvd] (qvd); |
then there'll be so many duplicate of a branch, such as :
branch_code | code | branch_name
---------------------------------------------------
45678 | 123 | AA Corp.
45678 | 231 | AA CORP
87654 | 321 | BBroom
87654 | 213 | BroomBroom
in fact, i only need :
branch_code | code | branch_name
---------------------------------------------------
45678 | 123 | AA Corp.
87654 | 321 | BBroom
============
so the question is :
how to put row of 'AA CORP' and 'BroomBroom' away with load script?
many thx
Hi Rendi,
You may try:
LOAD MID(code,4,5) AS branch_code, branch as branch_name, code |
FROM
[..\QlikView Data\QVD\M_BRANCH.qvd]
(qvd) Where Match(Left(code,3),123,321);
FROM [..\QlikView Data\QVD\M_BRANCH.qvd] (qvd); |
Anyhow, I think each table have status field for each branch in active or inactive. You may look into this and put condition accordingly.
Regards,
Sokkorn
is there any function to load based on minimal value of a field?
if i use match function like you mention, that mean i've to write it one-by-one manually, right?
If the example:
branch_code | code | branch_name
---------------------------------------------------
45678 | 123 | AA Corp.
45678 | 231 | AA CORP
87654 | 321 | BBroom
87654 | 213 | BroomBroom
is your actual data you cannot eliminate the duplicates because branch_name is different. This is a data quality issue which might better be resolved, if possible, in the source rather than in the QlikView load.
yes that's right.
but poorly my client doesn't has datawarehouse yet.
Rendi,
i would do the following. Please adapt to your case.
1) Numerate the lines with a recno() function for example
LOAD recno() as Line, .....
2) Do a LOAD Min(Line), Branch_Code resident YourTable in a temp table
3) Perform a Left join
See the example:
// A is like yoyr branch_code with different values A and B
Temp_Prod:
LOAD * INLINE [
Line, A, B, C
1, 123, X, Y
2, 123, A1, B1
3, 124, X, Z
4, 125, E, F
5, 125, EE, FF
6, 125, AAEEE, AAFFF
]
;
// Here, I get only the lines 1, 3, 4
Prod:
NoConcatenate
LOAD Min(Line) as Line,
A
Resident Temp_Prod
Group by A;
// With the Line Key, I am able to join the rest of the table and get the right info
Prod:
LEFT Join
LOAD *resident Temp_Prod;
drop tables Temp_Prod;