Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Count of days based on month

I have a table in below format and i need to calculate count of days based on month  taking admission and discharge date into account

IDAdmission DateDischarge date
11-Jan-115-Jan-11
126-Jan-112-Feb-11
130-Jan-112-Feb-11
27-Jan-1110-Jan-11
228-Feb-113-Mar-11

Ishould get the output in below format 

IDcount of daysmonth
113jan
12feb
24jan
21feb
23mar

please help me out in calculating this your quick response will progress my work fast its very urgent

Thanks in advance

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Not applicable

Re: Count of days based on month

See attached example.


talk is cheap, supply exceeds demand
3 Replies
Gysbert_Wassenaar
Not applicable

Re: Count of days based on month

See attached example.


talk is cheap, supply exceeds demand
Not applicable

Re: Count of days based on month

Thanks Gysbert for your answer it worked partially ....

Their is a problem in one thing if the records are duplicated i mean for same ID ,Admission date and discharge date are same ie  in two rows then it should count only once ,but it it is counting twice

can you please help me out in solving this as well.........

Gysbert_Wassenaar
Not applicable

Re: Count of days based on month

The best solution is to prevent duplicated records from being created in the first place. Spank the person that made that error. If that's not an option or the person persists in that perversion than you can try loading the source data with a load distinct somewhere:

T1:

load distinct *, month(Date) as Month;

load *, [Admission Date] + IterNo()-1 as Date

While [Admission Date] + IterNo()-1 <= [Discharge Date];

LOAD

          ID

          , date#([Admission Date],'D-MMM-YY') as [Admission Date]

          , date#([Discharge Date],'D-MMM-YY') as [Discharge Date]

INLINE [

    ID, Admission Date, Discharge Date

    1, 1-Jan-11, 5-Jan-11

   1, 26-Jan-11, 2-Feb-11

    1, 30-Jan-11, 2-Feb-11

    2, 7-Jan-11, 10-Jan-11

    2, 28-Feb-11, 3-Mar-11

   1, 26-Jan-11, 2-Feb-11   

];


talk is cheap, supply exceeds demand