topic Re: How to conditionally create or repeat rows based on condition ? in Water Cooler

How to conditionally create or repeat rows based on condition ?

shekhar_analyti — Mon, 14 Jan 2019 15:03:30 GMT

Hi All , How to conditionally create or repeat rows based on condition ? Input Data COMPANY SUB_COMPANY REGION YEAR RANK ABC AB X 2017 3 ABC AO Y 2014 8 ABC AO X 2016 4 XYZ AA Q 2015 3 PQR 21 X 2015 LAST OUTPUT NEEDED COMPANY SUB_COMPANY REGION YEAR RANK ABC AB X 2014 ABC AB X 2015 ABC AB X 2016 ABC AB X 2017 3 ABC AO Y 2014 8 ABC AO Y 2015 ABC AO Y 2016 ABC AO Y 2017 ABC AO X 2014 ABC AO X 2015 ABC AO X 2016 4 ABC AO X 2017 XYZ AA Q 2014 XYZ AA Q 2015 3 XYZ AA Q 2016 XYZ AA Q 2017 PQR 21 X 2014 PQR 22 X 2015 LAST PQR 23 X 2016 PQR 24 X 2017

Re: How to conditionally create or repeat rows based on condition ?

shekhar_analyti — Wed, 16 Jan 2019 11:28:57 GMT

@sunny_talwar ..

Sunny Bhai .. Please help

Re: How to conditionally create or repeat rows based on condition ?

sunny_talwar — Wed, 16 Jan 2019 12:18:25 GMT

Try this

Table:
LOAD Hash128(COMPANY&'|'&SUB_COMPANY&'|'&REGION&'|'&YEAR) as Key,
	 *;
LOAD * INLINE [
    COMPANY, SUB_COMPANY, REGION, YEAR, RANK
    ABC, AB, X, 2017, 3
    ABC, AO, Y, 2014, 8
    ABC, AO, X, 2016, 4
    XYZ, AA, Q, 2015, 3
    PQR, 21, X, 2015, LAST
];

MinMax:
LOAD MinYear + IterNo() - 1 as YEAR
While MinYear + IterNo() - 1 <= MaxYear;
LOAD Min(YEAR) as MinYear,
	 Max(YEAR) as MaxYear
Resident Table;

Left Join (MinMax)
LOAD DISTINCT COMPANY,
	 SUB_COMPANY,
	 REGION
Resident Table;

Concatenate (Table)
LOAD COMPANY,
	 SUB_COMPANY,
	 REGION,
	 YEAR
Resident MinMax
Where not Exists(Key, Hash128(COMPANY&'|'&SUB_COMPANY&'|'&REGION&'|'&YEAR));

DROP Table MinMax;

Re: How to conditionally create or repeat rows based on condition ?

shekhar_analyti — Wed, 16 Jan 2019 15:11:48 GMT

Thank You Bhai