Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi i have writed a qlikview script for an incremental table, but i have a problem with duplicated data.
The counter is right for data with date equal to date of execution of scipt, but this counter is double in the future execution.
SCRIPT:
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';
load max(date(START_DATE,'DD/MM/YYYY')) as LAST_DATE_GROUP
from
(QVD);
LET VCurrentMinStartDate = peek('LAST_DATE_GROUP') ;
load max(date(START_DATE,'DD/MM/YYYY')) as LAST_DATE_SUB
from
(QVD);
LET VCurrentMaxStartDate = peek('LAST_DATE_SUB') ;
if len('$(VCurrentMinStartDate)')=0 then
DATA_START:
LOAD OPERATOR_ID, MERCHANT_ID, PRODUCT_ID, date(START_DATE,'DD/MM/YYYY') as START_DATE, count(SUBSCRIPTION_ID) as NUM
FROM
(QVD)
WHERE date(START_DATE,'DD/MM/YYYY') < '$(VCurrentMaxStartDate)'
GROUP BY OPERATOR_ID, MERCHANT_ID, PRODUCT_ID, date(START_DATE,'DD/MM/YYYY')
;
STORE DATA_START into
ELSEIF '$(VCurrentMinStartDate)' <> date('$(VCurrentMaxStartDate)'-1, 'DD/MM/YYYY') then
DATA_START:
LOAD * from
DATA_START_NEW:
CONCATENATE(DATA_START)
LOAD OPERATOR_ID, MERCHANT_ID, PRODUCT_ID, date(START_DATE,'DD/MM/YYYY') as START_DATE, count(SUBSCRIPTION_ID) as NUM
FROM
(QVD)
WHERE (date(START_DATE,'DD/MM/YYYY') < '$(VCurrentMaxStartDate)' and date(START_DATE,'DD/MM/YYYY')> '$(VCurrentMinStartDate)')
GROUP BY OPERATOR_ID, MERCHANT_ID, PRODUCT_ID, date(START_DATE,'DD/MM/YYYY')
;
STORE DATA_START into
end if;
any solution?