Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I'm a newbie in Qlikview developpement a I have a Newbie question...
Is there a way to Store Old values of a Database Field in Qlikview before each script reload ?
Eg: When i load a field from my Progress Database, it return me the value of this moment. To compare this value to the value of yesterday, i need to store the value of yesterday somewhere before reloading the sript...
Thanks in advance (and sorry for my very bad English...I'm French)
Benoit
NbUVteLivLCC:
LOAD today() as DATE
NbUVteLivLCC as NbUVteLivLCC_SAVE //Quantité Livrée
RESIDENT LigneCC;
CONCATENATE
LOAD DATE, NbUVteLivLCC_SAVE
FROM NbUVteLivLCC_SAVE.qvd (qvd) ;
STORE NbUVteLivLCC into NbUVteLivLCC_SAVE.qvd (qvd) ;
As I told you in my first post, the concatenate part should not be present during the first load of the script, because the qvd file does not exist yet.
C'est bon pour toi ?
you can store a table in a qvd file :
store ta_table into ton_fichier.qvd (qvd) ;
Ok but how can I have an history of these old values ?
eg: 11/06/2013 value=10
10/06/2013 value = 9
09/06 2013 value = 4
etc
Thanks you
Build a table with date, value
use a script who looks like this :
table:
LOAD DATE, VALUE FROM yourSource ;
concatenate
LOAD DATE, VALUE from yourFile.qvd (qvd) ;
// supress this line the first time, because yourFile.qvd (qvd) does not exist yet
store table into yourFile.qvd (qvd) ;
If you need further help, share a file and I will help you
try this
day1:
load
myfield as [day1]
from table1;
store table1 into D:\day1.qvd;
drop table day1;
day2:
load
myfield as [day2]
from table;
store table1 into D:\day2.qvd;
drop table day1;
load
[day1]
from day1.qvd;
load
[day2]
from day2.qvd;
then you can easily compare your toady data from yesterday data
Thank you for you response, here is my qvs file:
//************************************************
// PARAMETRAGE
//************************************************
$(Include=param.qvs)
// Connection BD
ODBC CONNECT32 TO **********
//*******************************************************************
// Rapatriement Table BL
//*******************************************************************
BL:
LOAD IdBL,
IdCli,
NumBL,
Upper(TypeBL) as CodTypeBL,
EtatBL as CodEtatBL,
DateBL,
DateCreBL,
DateFacBL,
IdSalBenef,
IdDepPrinc,
CodPortBL as CodPort,
CPLivBL,
num (Left("CPLivBL",2)) as Dpt;
SQL SELECT IdBL, EtatBL, NumBL, DateBL, IdCli,DateCreBL, DateFacBL, TypeBL, IdSalBenef, IdDepPrinc, CodPortBL, CPLivBL
FROM PUB.BL where year(DateBL) >= 2012 and TypeBL <> 'Dep';
//**************
LigneBL:
LOAD IdBL,
IdLBL,
IdAD,
MontHTLBL,
IdLCC,
MontRevConvLBL,
MontHTLBL - MontRevConvLBL as MargeReLBL,
NbUStoLBL,
NbUVteLBL,
EtatLBL;
SQL SELECT IdBL, IdLBL, IdAD, IdLCC, MontHTLBL, MontRevConvLBL, NbUStoLBL, NbUVteLBL, EtatLBL
FROM PUB.LigneBL where year(DateCreLBL) >= 2012 and IdLCC <> 0;
//*******************************************************************
////*******************************************************************
// Rapatriement Table Commandes Client
//*********************************************************************
CC:
LOAD IdCC,
Upper(TypeCC) as CodTypeCC,
EtatCC as CodEtatCC,
DateCreCC,
NumCC,
IdDepPrinc as IdDepLivCC,
IdDepCre,
DateFacCC,
DateLivrCC,
CodPortCC,
CPLivCC;
SQL SELECT IdCC, EtatCC, IdDepPrinc, IdDepCre, NumCC, DateFacCC, DateCreCC, TypeCC, DateLivrCC, CodPortCC, CPLivCC
FROM PUB.CmdeCli where year(DateCreCC) >= 2012 and TypeCC <> 'Dep';
LigneCC:
LOAD IdLCC,
IdCC,
MontHTLCC,
MontRevConvLCC,
MontHTLCC - MontRevConvLCC as MargeReLCC,
NbUStoLCC,
NbUVteLCC, //Quantité commandée
NbUVteLivLCC, //Quantité Livrée
EtatLCC;
SQL SELECT IdLCC, IdCC, IdAD, MontHTLCC, MontRevConvLCC, NbUStoLCC, NbUVteLCC, NbUVteLivLCC, EtatLCC
FROM PUB.LigneCC where year(DateCreLCC) >= 2012;
Store CC into rals.qvd (qvd);
Store LigneCC into rals.qvd (qvd);
//*********************************************************************
////*******************************************************************
// Rapatriement Table Client
//*********************************************************************
Client:
LOAD IdCli,
RSocCli,
CodCli;
SQL SELECT IdCli, RSocCli, CodCli
FROM PUB.Cli;
//*********************************************************************
////*******************************************************************
// Rapatriement Table Dépot
//*********************************************************************
DepotBL:
LOAD IdDep as IdDepPrinc,
CodDep as CodDepLiv,
NomDep as NomDepLiv,
CodDep & '-' & NomDep as CodNomDepLiv;
SQL SELECT IdDep, CodDep, NomDep
FROM PUB.Depot;
DepotCC:
LOAD IdDep as IdDepLivCC,
IdDep as IdDepCreCC,
CodDep as CodDepLivCC,
NomDep as NomDepLivCC,
CodDep & '-' & NomDep as CodNomDepLivCC;
SQL SELECT IdDep, CodDep, NomDep
FROM PUB.Depot;
////*******************************************************************
// Rapatriement Table Article
//*********************************************************************
LOAD IdAD,
CodAD,
DesiAutoAD,
if (CodPrArt='ANC','oui','non') as FlgANCArt,
CodGesStkAD;
SQL SELECT ArtDet.IdAD, CodAD, DesiAutoAD, CodPrArt, CodGesStkAD
FROM PUB.ArtDet INNER JOIN PUB.ADFour ON ADFour.IdADF=ArtDet.IdADF INNER JOIN PUB.Art ON Art.IdArt=ArtDet.IdArt INNER JOIN PUB.ProtArt ON ProtArt.IdPrArt=Art.IdPrArt where IdDep <> 0
I would like to have an hystory of the "NbUVteLivLCC" field in the LigneCC table ....
thanks in advance
NbUVteLivLCC:
LOAD today() as DATE
NbUVteLivLCC as NbUVteLivLCC_SAVE //Quantité Livrée
RESIDENT LigneCC;
CONCATENATE
LOAD DATE, NbUVteLivLCC_SAVE
FROM NbUVteLivLCC_SAVE.qvd (qvd) ;
STORE NbUVteLivLCC into NbUVteLivLCC_SAVE.qvd (qvd) ;
As I told you in my first post, the concatenate part should not be present during the first load of the script, because the qvd file does not exist yet.
C'est bon pour toi ?
Impec, Merci beaucoup