Qlik Community

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

shekhar_analyti
Valued Contributor

How to conditionally create or repeat rows based on condition ?

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
1 Solution

Accepted Solutions

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

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;
3 Replies
shekhar_analyti
Valued Contributor

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

@sunny_talwar .. 

Sunny Bhai .. Please help 

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

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;
shekhar_analyti
Valued Contributor

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

Thank You Bhai