topic Re: populate data in QlikView

populate data

upaliwije — Fri, 04 Mar 2016 10:06:29 GMT

I have following data in my QV document

POLICY_NO	YEAR	PERIOD_FROM	PERIOD_TO	NEW_REN	LAST_REN_YEAR
7M00111C0000087	2012	21-Oct-2012	20-Oct-2013	REN	-
7M00111C0000087	2013	21-Oct-2013	20-Oct-2014	REN	2013
7M00141A0000245	2014	04-Nov-2014	03-Nov-2015	NEW	-
7M00141A0000245	2015	04-Nov-2015	03-Nov-2016	REN	2015
7M00141W0000022	2014	06-Mar-2014	05-Mar-2015	NEW	-
7M00141W0000022	2015	06-Mar-2015	05-Mar-2016	REN	2015
7M00131D0000089	2013	10-Jun-2013	09-Jun-2014	NEW	-
7M00131D0000089	2014	10-Jun-2014	09-Jun-2015	REN	-
7M00131D0000089	2015	10-Jun-2015	09-Jun-2016	REN	2015
7M00141A0000253	2014	31-Dec-2014	30-Dec-2015	NEW	-
7M00141A0000253	2015	31-Dec-2015	30-Dec-2016	REN	2015
7M00151D0000140	2015	08-Jan-2015	07-Jan-2016	NEW	-
7M00151D0000140	2016	08-Jan-2016	07-Jan-2017	REN	2016
7M00144F0000002	2014	29-Nov-2014	28-Nov-2015	NEW	-
7M00144F0000002	2015	29-Nov-2015	28-Nov-2016	REN	2015
7M00141C0000155	2014	28-Aug-2014	27-Aug-2015	NEW	-
7M00141C0000155	2015	28-Aug-2015	27-Aug-2016	REN	2015

LAST_REN_YEAR IS MISSING IN SOME RECORDS I WANT TO POPULATE THE MISSING LAST_REN_YEAR WITH LAST_REN_YEAR OF THE CORRESPONDING POLIICY_NO

EG:

POLICY_NO	YEAR	PERIOD_FROM	PERIOD_TO	NEW_REN	LAST_REN_YEAR
7M00111C0000087	2012	21-Oct-2012	20-Oct-2013	REN	2013
7M00111C0000087	2013	21-Oct-2013	20-Oct-2014	REN	2013

PLS HELP

Re: populate data

sunny_talwar — Fri, 04 Mar 2016 10:16:15 GMT

Looking to get this:

Script:

Table:

LOAD POLICY_NO,

YEAR,

PERIOD_FROM,

PERIOD_TO,

NEW_REN,

LAST_REN_YEAR

FROM

[https://community.qlik.com/thread/208165]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

NoConcatenate

LOAD POLICY_NO,

YEAR,

PERIOD_FROM,

PERIOD_TO,

NEW_REN,

If(POLICY_NO = Peek('POLICY_NO'), Peek('LAST_REN_YEAR'), LAST_REN_YEAR) as LAST_REN_YEAR

Resident Table

Order By POLICY_NO, YEAR desc;

DROP Table Table;

Re: populate data

maxgro — Fri, 04 Mar 2016 10:33:32 GMT

another one

Table:

LOAD POLICY_NO,

YEAR,

PERIOD_FROM,

PERIOD_TO,

NEW_REN,

LAST_REN_YEAR

FROM

[https://community.qlik.com/thread/208165]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

load POLICY_NO, max(YEAR) as NEWYEAR Resident Table Group by POLICY_NO;

DROP Field LAST_REN_YEAR;

RENAME Field NEWYEAR to LAST_REN_YEAR;

Re: populate data

upaliwije — Fri, 04 Mar 2016 10:35:16 GMT

Thanks Sir,

Always helpful

Looking forward to more help from a expert like you

Re: populate data

panosalexand — Tue, 08 Oct 2024 21:18:24 GMT

Hello all,

I have a similar issue and I can't find any solution yet. Let me narrate my story.

I have the following table

Data_temp:

Load
Center, GJAHR(is year), PERIO(is months in 001,002,003 format), Sales

From table1.qvd;

The problem is that Center has records lets say only for the Month 002 for 2024. How to enforce to display 0 Sales for the Center for the rest PERIOD?

How is now

Center	002
900001	1000,34

vs how I'd like to be

Center	001	002	003	004	005
900001	0	1000,34	0	0	0

Any idea?

P.S: I tried to join a dummy table with 0 as sales but I didn't get the desired results.

Thanks,
Panos