topic Re: Looping and generating new records in QlikView

Looping and generating new records

Wed, 03 Jun 2015 10:22:48 GMT

Hello

I've tried a few ways of doing this but I'd like some advice on the simplest method.

I have a table loaded in my QV script (see attached excel file).

For each unique PayrollNo there are 7 rows loaded with the Date field of each subsequent row being 1 calendar week after the previous. Different PayrollNo's can have different start dates but the same pattern of 6 subsequent weeks being loaded after remains.

What I'd like to do is add additional records for each PayrollNo with the below rules...

Take the latest loaded date for each PayrollNo and generate additional weekly records (previous Date + 7) and stops when Date >= 31/12/2015. Then repeat for next PayrollNo.

All other fields apart from Date should stay the same apart from TrainingWeek which should increase in +1 increments (W8, W9, W10). WeeklySalesTarget should always be 2 for all additional week records created.

Can someone please assist / advise?

Thanks

Adam

Re: Looping and generating new records

swuehl — Wed, 03 Jun 2015 10:37:44 GMT

Maybe something along this:

INPUT:

LOAD PayrollNo,

ResourceNameOrig,

pvTrainingStartDate,

pvTrainingFullyProductive,

TrainingWeek,

TrainingStage,

Date,

WeeklySalesTarget

FROM

[.\DateLoop.xlsx]

(ooxml, embedded labels, table is Sheet1);

TMP:

NOCONCATENATE

LOAD PayrollNo,

Date(max(Date)) as Date,

FirstSortedValue(ResourceNameOrig, -Date) as ResourceNameOrig,

FirstSortedValue(pvTrainingStartDate, -Date) as pvTrainingStartDate,

FirstSortedValue(pvTrainingFullyProductive, -Date) as pvTrainingFullyProductive,

mid(FirstSortedValue(TrainingWeek, -Date),2) as TrainingWeek,

FirstSortedValue(TrainingStage, -Date) as TrainingStage,

FirstSortedValue(WeeklySalesTarget, -Date) as WeeklySalesTarget

RESIDENT INPUT

GROUP BY PayrollNo

;

DROP TABLE INPUT;

RESULT:

NOCONCATENATE

LOAD

PayrollNo,

Date(Date+(iterno()-1)*7) as Date,

ResourceNameOrig,

pvTrainingStartDate,

pvTrainingFullyProductive,

'W' & (TrainingWeek+iterno()-1) as TrainingWeek,

TrainingStage,

WeeklySalesTarget

RESIDENT TMP

WHILE Date+(iterno()-1)*7 < MakeDate(2015,12,31);

drop table TMP;

Re: Looping and generating new records

Wed, 03 Jun 2015 10:52:57 GMT

Thanks Swuehl

I still needed to retain the records from the INPUT table so I've just concatenated the RESULT records to this original table.

And I also removed the "-1" from the Iterno() functions as I don't want W7 replicating.

Thanks again.

Adam

Re: Looping and generating new records

Wed, 03 Jun 2015 10:53:42 GMT

Hi Adam

You can use a While loop for this.

1. Find the latest 'Date' for each PayrollNo

2. Use a While clause to generate each week between the latest 'Date' and the end date you have specified

3. Concatenate the rows from 2. on to the original table

LatestDate:

LOAD

PayrollNo

,ResourceNameOrig

,pvTrainingStartDate

,pvTrainingFullyProductive

,TrainingStage

,Max(Right(TrainingWeek,1)) as MaxTrainingWeek

,Max(Date) as MaxDate

Resident

DateLoopTable

Group By

PayrollNo

,ResourceNameOrig

,pvTrainingStartDate

,pvTrainingFullyProductive

,TrainingStage

;

LET vEndDate = MakeDate(2015,12,31);

RowsToConcatenate:

LOAD

PayrollNo

,ResourceNameOrig

,pvTrainingStartDate

,pvTrainingFullyProductive

,TrainingStage

,'W' & MaxTrainingWeek+IterNo() as TrainingWeek

,MaxDate+(IterNo()*7) as Date

Resident

LatestDate

While

MaxDate+(IterNo()*7)<=$(vEndDate)

;

DROP Table LatestDate;

Concatenate(DateLoopTable)

LOAD

PayrollNo

,ResourceNameOrig

,pvTrainingStartDate

,pvTrainingFullyProductive

,TrainingStage

,TrainingWeek

,Date

,0 as WeeklySalesTarget // Or just leave as Null

Resident

RowsToConcatenate

;

DROP Table RowsToConcatenate;