topic Re: Change lolad from SQL select to QVD in QlikView

Change lolad from SQL select to QVD

b04nhu13 — Thu, 17 Apr 2014 06:04:20 GMT

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

Re: Change lolad from SQL select to QVD

its_anandrjs — Thu, 17 Apr 2014 06:14:00 GMT

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

Re: Change lolad from SQL select to QVD

b04nhu13 — Thu, 17 Apr 2014 06:19:57 GMT

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

Re: Change lolad from SQL select to QVD

its_anandrjs — Thu, 17 Apr 2014 06:25:15 GMT

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

Re: Change lolad from SQL select to QVD

b04nhu13 — Thu, 17 Apr 2014 06:46:32 GMT

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

Re: Change lolad from SQL select to QVD

its_anandrjs — Thu, 17 Apr 2014 06:52:41 GMT

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

Re: Change lolad from SQL select to QVD

b04nhu13 — Thu, 17 Apr 2014 07:58:18 GMT

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.

Re: Change lolad from SQL select to QVD

its_anandrjs — Thu, 17 Apr 2014 08:06:59 GMT

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.

Re: Change lolad from SQL select to QVD

b04nhu13 — Thu, 17 Apr 2014 08:14:54 GMT

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.

Re: Change lolad from SQL select to QVD

jonathandienst — Thu, 17 Apr 2014 08:24:50 GMT

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

Re: Change lolad from SQL select to QVD

b04nhu13 — Thu, 17 Apr 2014 09:08:40 GMT

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

Re: Change lolad from SQL select to QVD

its_anandrjs — Thu, 17 Apr 2014 09:20:25 GMT

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;

Re: Change lolad from SQL select to QVD

jonathandienst — Thu, 17 Apr 2014 09:58:00 GMT

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

Re: Change lolad from SQL select to QVD

b04nhu13 — Thu, 17 Apr 2014 11:28:47 GMT

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