Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ..
How can i load month wise data from mssql in Qlikview..
My script is
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=IVR_REPORTING;Data Source=172.16.6.237;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=IN1DT0536;Use Encryption for Data=False;Tag with column collation when possible=False];
IVRREPORTDATA_CALLINFO:
SELECT Top 100000
CallID,
//UCCX_SESSID,
ApplicationID,
CallDateTime,
CAST( CallDateTime as date) as Date_DATE_TIME,
CAST( CallDateTime as time) as Time_DATE_TIME,
SessionID,
Status,
p.value('(./ABANDONED_FLAG)[1]', 'VARCHAR(8000)') AS ABANDONED_FLAG,
p.value('(./ICR)[1]', 'VARCHAR(8000)') AS ICR,
p.value('(./LANGCODE)[1]', 'VARCHAR(8000)') AS LANGCODE,
p.value('(./LASTMENUACCESSED)[1]', 'VARCHAR(8000)') AS LASTMENUACCESSED,
p.value('(./RTR_SEQ_NUM)[1]', 'VARCHAR(8000)') AS KEY_COLUMN,
p.value('(./SERVICEPATH)[1]', 'VARCHAR(8000)') AS SERVICEPATH,
p.value('(./SESSIONID)[1]', 'VARCHAR(8000)') AS SESSIONID,
p.value('(./RESERVE1)[1]', 'VARCHAR(8000)') AS RESERVE1,
p.value('(./RESERVE2)[1]', 'VARCHAR(8000)') AS RESERVE2,
p.value('(./RESERVE3)[1]', 'VARCHAR(8000)') AS RESERVE3,
p.value('(./RESERVE4)[1]', 'VARCHAR(8000)') AS RESERVE4,
p.value('(./RESERVE5)[1]', 'VARCHAR(8000)') AS RESERVE5,
p.value('(./RESERVE6)[1]', 'VARCHAR(8000)') AS RESERVE6,
p.value('(./RESERVE7)[1]', 'VARCHAR(8000)') AS RESERVE7,
p.value('(./RESERVE8)[1]', 'VARCHAR(8000)') AS RESERVE8,
p.value('(./RESERVE9)[1]', 'VARCHAR(8000)') AS RESERVE9,
p.value('(./RESERVE10)[1]', 'VARCHAR(8000)') AS RESERVE10
FROM "IVR_REPORTING".dbo."TBL_CALLDATA_MASTER_TEMP"
CROSS APPLY CallData.nodes('/IVRREPORTDATA/CALLINFO') as t(p);
STORE IVRREPORTDATA_CALLINFO into IVRREPORTDATA_CALLINFO.qvd;
i want to get month wise data based on the Date_DATE_TIME.
please help me..
Thanks & Regards,
Chandini.G
Hi Chandini
You could either use the QlikView in-house function MonthStart() as a preceeding load to divide the dates up into the months:
IVRREPORTDATA_CALLINFO:
Load *, Monthstart(Date_DATE_TIME) as Month_DATE_TIME;
SELECT Top 100000
CallID,
...
Or load it in using a SQL nativefunction
DATEADD(MONTH, DATEDIFF(MONTH, 0, Date_DATE_TIME), 0) as Month_DATE_TIME,
I'm assuming you want to divde the dates up into months, if you want the month name or number you would need touse different functions. Is this what you are after?
Erica
Hi erica..
Thanks for your response..
What my exact need is i have july and aug month data in Single database.i want to create 2 qvds, one for july month data and second is aug related data....
Please help me..
Regards,
Chandini.G