topic Re: Help:To build report in QlikView

Help:To build report

manojkulkarni — Wed, 25 Nov 2020 16:16:04 GMT

Hi Friends,

I am looking for solution/suggestion in building weekly report using below sample data.

Requirement:

We have Activity details captured on daily basis. Activity can be started & completed on same day or it might carry forwarded to next day. I need to prepare a weekly report on this data to show how many activities received and completed in a week. I am not able to get the report for the cases which are started in week1 and closed in week2. How to achieve this ?

Sample Data

ActivityID	ActivityDesc	Start Date	End Date
A01	Test1	03-01-2017	03-01-2017
A02	Test2	03-01-2017	05-01-2017
A03	Test3	03-01-2017	10-01-2017
A04	Test4	04-01-2017	06-01-2017
A05	Test5	04-01-2017	04-01-2017
A06	Test6	05-01-2017	05-01-2017
A07	Test7	06-01-2017	10-01-2017
A08	Test8	03-01-2017	09-01-2017
A09	Test9	09-01-2017	09-01-2017
A10	Test10	10-01-2017	12-01-2017

Expected Out put

	Started	Closed
Week1 (02 Jan to 6th Jan 2017)	8	5
Week2 (09 Jan to 13th Jan 2017)	2	5

Re: Help:To build report

varshavig12 — Thu, 19 Jan 2017 09:11:26 GMT

Try this:

Load ActivityID ,ActivityDesc ,date#([Start Date],'DD-MM-YYYY') as StartDate ,date#([End Date],'DD-MM-YYYY') as EndDate,

Right(WeekName(date#([Start Date],'DD-MM-YYYY')),2) as WeekS,

Right(WeekName(date#([End Date],'DD-MM-YYYY')),2) as WeekC

;

LOAD * INLINE [

ActivityID ,ActivityDesc ,Start Date ,End Date

A01 ,Test1 ,03-01-2017 ,03-01-2017

A02 ,Test2 ,03-01-2017 ,05-01-2017

A03 ,Test3 ,03-01-2017 ,10-01-2017

A04 ,Test4 ,04-01-2017 ,06-01-2017

A05 ,Test5 ,04-01-2017 ,04-01-2017

A06 ,Test6 ,05-01-2017 ,05-01-2017

A07 ,Test7 ,06-01-2017 ,10-01-2017

A08 ,Test8 ,03-01-2017 ,09-01-2017

A09 ,Test9 ,09-01-2017 ,09-01-2017

A10 ,Test10 ,10-01-2017 ,12-01-2017

];

Mapping Load ActivityID,WeekC

resident a;

Load ActivityID ,ActivityDesc, 'C' as flag,

applymap('b',ActivityID) as Week

Resident a;

Mapping Load ActivityID,WeekS

resident a;

Load ActivityID ,ActivityDesc, 'S' as flag,

applymap('c',ActivityID) as Week

Resident a;

load ActivityID,StartDate,EndDate

Resident a;

drop table a;

Expression: count({<flag={'S'}>}ActivityID)

count({<flag={'C'}>}ActivityID)

Re: Help:To build report

Anonymous — Thu, 19 Jan 2017 09:13:33 GMT

Hi Manoj,

Please find following:

Change In Script:-

RawData:

LOAD * INLINE [

ActivityID, ActivityDesc, Start Date, End Date

A01, Test1, 03-01-2017, 03-01-2017

A02, Test2, 03-01-2017, 05-01-2017

A03, Test3, 03-01-2017, 10-01-2017

A04, Test4, 04-01-2017, 06-01-2017

A05, Test5, 04-01-2017, 04-01-2017

A06, Test6, 05-01-2017, 05-01-2017

A07, Test7, 06-01-2017, 10-01-2017

A08, Test8, 03-01-2017, 09-01-2017

A09, Test9, 09-01-2017, 09-01-2017

A10, Test10, 10-01-2017, 12-01-2017

];

mainData:

load * ,

WeekStart(date#([Start Date],'DD-MM-YYYY')) AS Start_Week,

WeekStart(date#([End Date],'DD-MM-YYYY')) as End_Week,

WeekStart(date#([Start Date],'DD-MM-YYYY')) as Date1

Resident RawData;

DROP table RawData;

UI table:-

Start_Week	End_Week	Count(ActivityID)
1/2/2017	1/2/2017	5
	1/9/2017	3
	Total	8
1/9/2017	1/9/2017	2
1/9/2017	Total	2
Total		10

Let me know if above is working for you

Thanks,

Pooja