How to repeat number of rows X times during load based where value of X is in one of the field ?

[shekhar_analyti]

Hi All ,

This is my first post , post community modification .

Problem :

How to repeat number of rows X times during load based where value of X is in one of the field ?

INPUT 

LOAD * INLINE [
Year, ID, Country, Sales, MARK
2015, EMPID001, CHINA, 200, 1
2016, EMPID002, RUSSIA, 50, 3              - Repeat it three times starting with 1 ending 3 
2017, EMPID003, INDIA, 400, 4                -   Repeat it four times starting with 1 ending 4
2018, EMPID004, US, 600, 1
];

OUTPUT 

Year	ID	Country	Sales	New_Mark
2015	EMPID001	CHINA	200	1
2016	EMPID002	RUSSIA	50	1
2016	EMPID002	RUSSIA	50	2
2016	EMPID002	RUSSIA	50	3
2017	EMPID003	INDIA	400	1
2017	EMPID003	INDIA	400	2
2017	EMPID003	INDIA	400	3
2017	EMPID003	INDIA	400	4
2018	EMPID004	US	600	1

Thanks & Regards

Shekar

[Gysbert_Wassenaar · Accepted solution]

Try this:

MyTable:
LOAD *, IterNo() as New_Mark While Iterno() <= MARK;
LOAD * INLINE [
Year, ID, Country, Sales, MARK
2015, EMPID001, CHINA, 200, 1
2016, EMPID002, RUSSIA, 50, 3
2017, EMPID003, INDIA, 400, 4
2018, EMPID004, US, 600, 1
];

[kaushiknsolanki]

Hi,

Use below script;

Data:
LOAD *,'1-'&MARK as KEY INLINE [
Year, ID, Country, Sales, MARK
2015, EMPID001, CHINA, 200, 1
2016, EMPID002, RUSSIA, 50, 3
2017, EMPID003, INDIA, 400, 4
2018, EMPID004, US, 600, 1
];

Max:
Load Max(MARK) as MAX
Resident Data;

Let vMax = Peek('MAX',0,'Max');

Autogenerate:
Load RowNo() as Number
AutoGenerate $(vMax);

Interval:
IntervalMatch(Number)
Load '1' as Start,MARK as END
Resident Data;

Left join(Data)
Load Start&'-'&END as KEY,Number
Resident Interval;

Drop table Interval,Autogenerate;

Regards,

Kaushik Solanki