Grouping calculation

[imallears]

Hi,

I'm hoping this is simple.

I want to group the Instrument with Start and End Dates, and ultimately calculate the total number of weeks attached to each.

I have attached two screenshots below. The 1st is the raw dataset, and the 2nd is what I want to achieve in Qlikview. How do I do it?? Any help would be great.

Thanks

Dataset

Expected Results

 

[vishsaggi]

What is the grouping logic you used here? On what criteria you considered Drums which is coming twice?

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD Name,

    Instrument,

    [Start Date],

    [End Date],

    [number of weeks]

FROM

[..\..\..\Downloads\band data.xls]

(biff, embedded labels, table is Sheet1$);

TempTable:

LOAD If(Name = Previous(Name), If(Instrument = Previous(Instrument), Peek('Sort'), RangeSum(Peek('Sort'), 1)), 1) as Sort,

*

Resident Table

Order By Name, [Start Date] desc;

FinalTable:

NoConcatenate

LOAD Name,

Instrument,

Sort,

Min([Start Date]) as [Start Date],

Max([End Date]) as [End Date],

Sum([number of weeks]) as [number of weeks]

Resident TempTable

Group By Name, Instrument, Sort;

DROP Tables Table, TempTable;

[imallears]

Excellent. Thank you Sunny