Change lolad from SQL select to QVD

[b04nhu13]

Change load from SQL to QVD and using MAX
Currently we load data in to a QVD and we take the MAX value by using SQL, like this.

*********************************************
SP115F:
LOAD
num(BLLAN,00) & '_' & num(BLKMN,00) & '_' & num(BLFOR,00) as LKF,
num(BLLAN,00) & '_' & num(BLKMN,00) as LK,
[00002] as Kommunnamn,
[00003] as "Församlingsnamn";
SQL SELECT
max(FRMDT),
max(KMNAM),
max(FONAM),
BLLAN,
BLKMN,
BLFOR
FROM $(_LFLFDATA).SP115F
GROUP BY BLLAN,BLKMN,BLFOR;

****************************************** 

But now I have to load from QVD file and do the same as SQL Selcet MAX do.
The field FRMDT is a date field (20050101) and I want the latest.
**************
SP115F:
Load
FRMDT, max(FRMDT),
Kommunnamn, max(Kommunnamn),
Forsamlingsnamn, max(Forsamlingsnamn),
LKF,
LK
GROUP BY BLLAN,BLKMN,BLFOR;

LOAD
num(BLLAN,00) & '_' & num(BLKMN,00) & '_' & num(BLFOR,00) as LKF,
num(BLLAN,00) & '_' & num(BLKMN,00) as LK,
KMNAM as Kommunnamn,
FONAM as "Forsamlingsnamn",
BLLAN,
BLKMN,
BLFOR,
FRMDT
From $(QVDB)SP115F.qvd (qvd);
********************************

[b04nhu13]

That works perfect.

But it is like you say I need a table that contains the max values and I am not sure how to do that

[its_anandrjs]

Same in my earlier post i explain you can load like this also

SP115F:
Load

max(FRMDT) as FRMDT,

max(KMNAM) as KMNAM,

max(FONAM) as FONAM,

Kommunnamn,
Forsamlingsnamn,
LKF,
LK
BLLAN,
BLKMN,
BLFOR,
From $(QVDB)SP115F.qvd (qvd)

GROUP BY BLLAN,BLKMN,BLFOR;

[jonathandienst]

Hi

This is what you need. The first loads the underlying detail and the second joins the max values:

SP115F:

Load

    BLLAN,        // Key for join

    BLKMN,        // Key for join

    BLFOR,        // Key for join

    num(BLLAN,00) & '_' & num(BLKMN,00) & '_' & num(BLFOR,00) as LKF,

    num(BLLAN,00) & '_' & num(BLKMN,00) as LK,

    KMNAM as Kommunnamn,

    FONAM as "Forsamlingsnamn",

    FRMDT

From $(QVDB)SP115F.qvd (qvd);

Join(SP115F)

Load

    BLLAN,        // Key for join

    BLKMN,        // Key for join

    BLFOR,        // Key for join

    max(FRMDT) As As MaxFRMDT

    max(KMNAM) As MaxKommunnamn,

    max(FONAM) As MaxForsamlingsnamn

From $(QVDB)SP115F.qvd (qvd)

GROUP BY BLLAN,BLKMN,BLFOR;

HTH

Jonathan

[b04nhu13]

Thanks for the help

This works for me;

TempLKF:

Load

    BLLÄN,        // Key for join

    BLKMN,        // Key for join

    BLFÖR,        // Key for join

    max(FRMDT) as FRMDTA

//    max(KMNAM) As Kommunnamn,

//    max(FÖNAM) As Församlingsnamn

From $(QVDB)SP115F.qvd (qvd)

GROUP BY BLLÄN,BLKMN,BLFÖR;

inner Join(SP115F)

Load

    FRMDT AS FRMDTA,

    BLLÄN,        // Key for join

    BLKMN,        // Key for join

    BLFÖR,        // Key for join

    num(BLLÄN,00) & '_' & num(BLKMN,00) & '_' & num(BLFÖR,00) as LKF,

    num(BLLÄN,00) & '_' & num(BLKMN,00) as LK,

    KMNAM as Kommunnamn,

    FÖNAM as "Församlingsnamn"

From $(QVDB)SP115F.qvd (qvd);