topic Generating fields according to other table's values in QlikView

Generating fields according to other table's values

kira_whopper — Thu, 06 Feb 2014 19:19:07 GMT

Hi there,

I'm having some trouble trying to solve this issue:

I have the vendor table with these fields:

CODE_VENDOR	DATE_ADM
01	26/06/2013
02	10/09/2013
03	15/10/2013
04	20/10/2013
05	19/11/2013

And the periods table with these fields:

Period_Start	Period_End
25/06/2013	20/12/2013
24/07/2013	23/01/2014
24/08/2013	23/02/2014
25/09/2013	24/03/2014
24/10/2013	22/04/2014
26/11/2013	25/05/2014
21/12/2013	19/06/2014
24/01/2014	23/07/2014

What I need to to is create records for each vendor, starting from the first period available until the last registered.

That would generate this for vendor 01, for example:

CODE_VENDOR	DATE_ADM	Period_Start	Period_End
01	26/06/2013	25/06/2013	20/12/2013
01	26/06/2013	21/12/2013	19/06/2014

Each period is 6months large.

Any ideas?

Re: Generating fields according to other table's values

Thu, 06 Feb 2014 19:26:40 GMT

Hi Marcos

I think IntervalMatch may be what you're looking for

Lukasz

Re: Generating fields according to other table's values

brindlogcool — Thu, 06 Feb 2014 20:03:49 GMT

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates

It will be helpful...

Re: Generating fields according to other table's values

Fri, 07 Feb 2014 06:39:59 GMT

Below script may help you ...

Vendor:

LOAD * INLINE [
CODE_VENDOR, DATE_ADM
01, 26/06/2013
02, 10/09/2013
03, 15/10/2013
04, 20/10/2013
05, 19/11/2013
];

Period:
LOAD * Inline [
Period_Start, Period_End
25/06/2013, 20/12/2013
24/07/2013, 23/01/2014
24/08/2013, 23/02/2014
25/09/2013, 24/03/2014
24/10/2013, 22/04/2014
26/11/2013, 25/05/2014
21/12/2013, 19/06/2014
24/01/2014, 23/07/2014
];

IntervalMatch(DATE_ADM)
LOAD Period_Start, Period_End
Resident Period;

Re: Generating fields according to other table's values

kira_whopper — Fri, 07 Feb 2014 20:06:48 GMT

Thanks,

Using what you suggested and some other stuff I looked into qlikview documentation, I figured out the solution.

Hope it can help you with similar issues o/