Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day!
In attached file i have some test model.
When i reload it every day i need to fill such table:
Spoint Date Value
Spoint1 01-01-2014 1 <--------------------When i done reload 01.01.2015
Spoint2 01-01-2014 1
+
Spoint1 02-01-2014 1 <--------------------When i done reload 02.01.2015
Spoint2 02-01-2014 1
+
Spoint1 03-01-2014 1 <--------------------When i done reload 03.01.2015
Spoint2 03-01-2014 1
+
Spoint1 04-01-2014 1 <--------------------When i done reload today
Spoint2 04-01-2014 1
+
Spoint1 05-01-2014 1 <--------------------When i done reload tomorrow
Spoint2 05-01-2014 1
In other words, i want to add values 1 for each Spoints for dates between firts date of current month and today date (example in xls file)
Many thanks for advices.
You need to change your script to use Incremental Load.
There is a simple example in this threadIncremental reload and store into QVD file
There are many more articles on the QlikView Community about this, and in the reference manuals.
Thanks for your answer.
If you have some time, could you help me to solve my task in example?
Many thanks..
Hi,
You can use Incremental load concept here for create the rows for the SPoint 1 and 2 and try with this script
1. First load this tables and store the table in QVD
ReloadDetails:
LOAD
ReloadTime() as ReloadTime,
'point 1' as Spoint,
1 as Value
AutoGenerate 1;
LOAD
ReloadTime() as ReloadTime,
'point 2' as Spoint,
1 as Value
AutoGenerate 1;
STORE ReloadDetails into ReloadDetails.qvd;
2. Then load the store qvd table and then concatenate next day
ReloadDetails:
LOAD
Date(ReloadTime(),'DD-MM-YYYY') as ReloadTime,
'point 1' as Spoint,
1 as Value
AutoGenerate 1;
LOAD
Date(ReloadTime(),'DD-MM-YYYY') as ReloadTime,
'point 2' as Spoint,
1 as Value
AutoGenerate 1;
Concatenate(ReloadDetails)
New:
LOAD ReloadTime,
Spoint,
Value
FROM ReloadDetails.qvd (qvd);
STORE ReloadDetails into ReloadDetails.qvd;
Regards
Anand
Thanks for answer.
It's very useful for example, where i have 2 Spoints. In my work model, for example, i will have more than 20 Spoints and i think, it's not very simple for such situation.
Thank you!
Hi,
Then try this in the first step
1. First create the QVD with find Max point value in the table suppose it is max upto 10 also
MaxPoint:
LOAD Max(PurgeChar(Spoint,'point')) as MaxPointNum Resident Input;
LET vMax = Peek('MaxPointNum',0,'MaxPoint');
ReloadDetails:
LOAD
Date(ReloadTime(),'DD-MM-YYYY') as ReloadTime,
'point ' &IterNo() as Spoint,
1 as Value
AutoGenerate 1 While IterNo() <= $(vMax);
STORE ReloadDetails into ReloadDetails.qvd;
2. And in this step concatenate the
MaxPoint:
LOAD Max(PurgeChar(Spoint,'point')) as MaxPointNum Resident Input;
LET vMax = Peek('MaxPointNum',0,'MaxPoint');
ReloadDetails:
LOAD
Date(ReloadTime(),'DD-MM-YYYY') as ReloadTime,
'point ' &IterNo() as Spoint,
1 as Value
AutoGenerate 1 While IterNo() <= $(vMax);
Concatenate(ReloadDetails)
LOAD ReloadTime,
Spoint,
Value
FROM
ReloadDetails.qvd
(qvd);
STORE ReloadDetails into ReloadDetails.qvd;
Regards
Anand
Thanks for answer.
I found other solution to rewrite records by last actual date.
SecondLoad:
LOAD Spoint as Data1,
Date,
Value
FROM
(ooxml, embedded labels, table is Sheet1)
WHERE (Date <= $(lastReloadTime));
But after reload it show me error:
Error in expression: ')' expected
Maybe you have some ideas to solve it ?
You can use it simply
WHERE Date <= $(lastReloadTime);
And what is the definition of the lastReloadTime time variable and format of the Date and lastReloadTime variable will it match to each other not.
Regards
Anand