topic Re: [ASK] COUNT and MIN in load table in QlikView

[ASK] COUNT and MIN in load table

Thu, 23 Jan 2014 07:32:38 GMT

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

Re: [ASK] COUNT and MIN in load table

Sokkorn — Thu, 23 Jan 2014 07:49:40 GMT

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

Re: [ASK] COUNT and MIN in load table

Thu, 23 Jan 2014 09:25:51 GMT

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?

Re: [ASK] COUNT and MIN in load table

Michiel_QV_Fan — Thu, 23 Jan 2014 09:31:56 GMT

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.

Re: [ASK] COUNT and MIN in load table

Thu, 23 Jan 2014 09:38:56 GMT

yes that's right.

but poorly my client doesn't has datawarehouse yet.

Re: [ASK] COUNT and MIN in load table

Thu, 23 Jan 2014 09:52:20 GMT

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;