Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Loading month wise data from database in Qlikview?

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

2 Replies
Not applicable
Author

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

Anonymous
Not applicable
Author

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