[ASK] COUNT and MIN in load table

[#1]

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

LOAD MID(code,4,5) AS branch_code, desc, value

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

[Sokkorn]

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

LOAD MID(code,4,5) AS branch_code, desc, value

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

[#3]

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?

[Michiel_QV_Fan]

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.

[#5]

yes that's right.

but poorly my client doesn't has datawarehouse yet.

[#6]

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;