Hi,
I have created a QV document with the script below and I am having issues below:
- Date function (Date, Year, Month,Day, Weekday) is not functioning properly because all the dates are available for selection. The only dates that must be available must be the ones aligned to available data from the sources and irrelevant dates must be grayed out.
- How do I create a daily average from my date range (2013/10/01 to 2014/03/18)?
- How do I set qvd's to update daily as I receive daily files and I want those files to be included onto the QVD?
- How do I publish the Dashboard onto Qlikview Server and set it to automatically update and reload at a certain time?
I have a qlikview server and I want to setup everything to run every morning. Please advise?
Set sDate = makedate(2012,12,31);
Set eDate = makedate(2014,12,31);
AGENCY_MASTER_TEMP:
LOAD [Loc number] as LOC_MASTER,
[Loc number] as LOC_EVENTS,
[Loc number] as LOC_JOURNAL,
[Loc number] as LOC_OTILL,
[Agency Name],
Area,
TRIM([Agency Name]) & '_' & TRIM(Area) as LOC_KIOSK,
Region,
1 as JoinKey
FROM
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
//STORE AGENCY_MASTER INTO \\03rnb-qlkvw01\D\QVData\Elija's_QVD's\Agency_Master.qvd (QVD);
left join
Load
1 as JoinKey,
Date($(sDate) + IterNo(), 'YYYY-MM-DD') as AgencyDate
AUTOGENERATE(1)
While $(sDate) + IterNo() <= $(eDate);
Drop Field JoinKey;
AGENCY_MASTER:
NoConcatenate
load *
, LOC_EVENTS & '_' & AgencyDate as EVENTS_JOINKEY
, LOC_JOURNAL & '_' & AgencyDate as JOURNAL_JOINKEY
, LOC_OTILL & '_' & AgencyDate as OTILL_JOINKEY
, LOC_KIOSK & '_' & AgencyDate as KIOSK_JOINKEY
resident AGENCY_MASTER_TEMP;
DROP Table AGENCY_MASTER_TEMP;
Events:
LOAD [Customer Nr],
Date_EVENTS,
Event_Time,
[Event Nr],
[Event Name],
Product,
[Device Nr],
[Reason Description],
Username,
[Full Name],
[Event Location],
EventFlag,
Name,
Cell,
Email,
Events_Region,
[Location No],
EVENTS_JOINKEY
FROM
[\\03rnb-qlkvw01\QVData\Elija's_QVD's\Events.qvd]
(qvd);
Journals:
LOAD [Customer Number],
[Journal Amount],
DebitFlag,
CreditFlag,
SMS,
[Journal Type],
[User Name],
[User Full Name],
JOURNAL_JOINKEY,
[Date Created],
Date_JOURNAL,
Action,
[Date Approved],
Approval_Date,
[Journals Status],
[First Name],
Surname,
[Account Number],
[Journal Location],
JournalFlag,
[Action Location],
[Customer Type],
[Account Type]
FROM
[\\03rnb-qlkvw01\QVData\Elija's_QVD's\Journals.qvd]
(qvd);
OnlineTill:
LOAD Date_OTILL,
User_Details,
Shift_ID,
OTILL_JOINKEY,
BD_Number,
Transaction_Count,
Credit_Card,
Cash,
Cheque,
Total_Amount
FROM
[\\03rnb-qlkvw01\QVData\Elija's_QVD's\OnlineTill.qvd]
(qvd);
Set sDate = makedate(2012,12,31);
Set eDate = makedate(2014,12,31);
KIOSK_MASTER_TEMP:
LOAD Kiosk_Name,
Kiosk_Location,
Area as Kiosk_Area,
Region as Kiosk_Region,
TRIM(Kiosk_Location) & '_' & TRIM(Area) as LOC_KIOSK,
1 as JoinKey
FROM
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
left join
Load
1 as JoinKey,
Date($(sDate) + IterNo(), 'YYYY-MM-DD') as KioskDate
AUTOGENERATE(1)
While $(sDate) + IterNo() <= $(eDate);
Drop Field JoinKey;
KIOSK_MASTER:
NoConcatenate
load *
, LOC_KIOSK & '_' & KioskDate as KIOSK_JOINKEY
, Kiosk_Name & '_' & KioskDate as KIOSK_JOINKEY_D
resident KIOSK_MASTER_TEMP;
Drop Field LOC_KIOSK;
DROP Table KIOSK_MASTER_TEMP;
Kiosk_Payments:
LOAD //TxnID,
DateTime,
Amount,
//CardClass,
//CardHolderName,
//TransactionStatusCode,
// TerminalID,
//MerchantNumber,
//AccountType,
//PrimaryAccountNumber,
//ResponseCode,
//ResponseCodeText,
//Status,
//StatusReason,
//accountNumber,
customerNumber,
Kiosk_Time,
[Receipt Number],
KName,
KLocation,
KRegion,
RetrievalReferenceNumber,
KIOSK_JOINKEY_D
FROM
[\\03rnb-qlkvw01\QVData\Elija's_QVD's\Kiosk_Payments.qvd]
(qvd);
