Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
chandinig
Creator
Creator

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

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

chandinig
Creator
Creator

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