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

[its_anandrjs]

I believe you allready get max values you have to need to load only qvd.

[b04nhu13]

I need to clarify. I don’t have the original QVD with SQL select. The QVD is now just a table that contains all values. All data from SQL without any changes

[its_anandrjs]

I believe you are not get the correct QVD after max and group by because in sql query your not give proper name like

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) as FRMDT, <<---- give alias name
max(KMNAM) as KMNAM, <<---- give alias name
max(FONAM) as FONAM, <<---- give alias name
BLLAN,
BLKMN,
BLFOR
FROM $(_LFLFDATA).SP115F
GROUP BY BLLAN,BLKMN,BLFOR;

Try to check this script

[b04nhu13]

Sorry. The SQL script is OK.
It is when I try to load from the QVD with same name but now the qvd include all data.

I now I want to get same values from QVD as we did when we load it from SQL

[its_anandrjs]

Ok then dont use group by and the max in the qvd load

Try to load like

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

and please provide exactly the field name from the QVD what you get from the SQL load finally.

Regards

[b04nhu13]

Yes that works just fine, but I don't want all values from FRMDT only the latest date, because the other fields contains same values and KMNAM and FONAM me be different.

[its_anandrjs]

In your SQL load you are able to do this like

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",

Where FRMDT >= Max(FRMDT); //Add this line

SQL SELECT
max(FRMDT) as FRMDT, <<---- give alias name
max(KMNAM) as KMNAM, <<---- give alias name
max(FONAM) as FONAM, <<---- give alias name
BLLAN,
BLKMN,
BLFOR
FROM $(_LFLFDATA).SP115F
GROUP BY BLLAN,BLKMN,BLFOR;

and after this you get latest lines based on the FRMDT.

[b04nhu13]

Yes, I know that. But I can't load it from SQL anymore. I have to load it from a QVD file. When the QVD is created from SQL they have removed all transformation (max) so it is a mirror of the SQL table.

[jonathandienst]

Hi

I dont think you need a preceding load here. So just this:

SP115F:

Load

  max(FRMDT) As FRMDT

  max(KMNAM) As Kommunnamn,

  max(FONAM) As Forsamlingsnamn,

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

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

  BLLAN,

  BLKMN,

  BLFOR

From $(QVDB)SP115F.qvd (qvd)

GROUP BY BLLAN,BLKMN,BLFOR;

Note that you cannot load Sum(FRMDT) and FRMDT in the same load statement so I have removed these. If you need a table in your model that contains the Max value and the underlying values, then you will need to load once without grouping to load the underlying values and then join the Group By load to add the aggregated values.

HTH

Jonathan