Loading month wise data from database in Qlikview?

Anonymous — Mon, 29 Sep 2014 08:58:56 GMT

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

Re: Loading month wise data from database in Qlikview?

Mon, 29 Sep 2014 10:04:36 GMT

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

Re: Loading month wise data from database in Qlikview?

Anonymous — Wed, 01 Oct 2014 06:38:09 GMT

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

topic Re: Loading month wise data from database in Qlikview? in QlikView

Loading month wise data from database in Qlikview?

Re: Loading month wise data from database in Qlikview?

Re: Loading month wise data from database in Qlikview?