Group by status in load script

[nburton78]

I currently have data that comes in like this from an excel document:

Order Number	Status	Date
1	10	12/1/2019
1	20	12/2/2019
1	30	12/2/2019
1	40	12/3/2019
1	50	12/4/2019
1	60	12/6/2019
1	70	12/6/2019
2	10	10/1/2019
2	20	10/2/2019
2	30	10/10/2019
2	40	10/12/2019
2	50	10/14/2019
2	60	10/16/2019
2	70	10/16/2019

For each order number I want to load the date it was at a certain status, kinda like this.  

	ten status	twentystatus	thirtystatus
order Number 1	12/1/2019	12/2/2019	12/2/2019
order Number 2	10/1/2019	10/2/2019	10/10/2019

I want to do this in the load script, but i'm not sure how it should be done.  Right now all I have is this:

"LOAD [SO Number]&[Order Type] & ([Line Number]*1000) as [Order Number], [WO
Status] as Status, Min as Date
FROM [LineLevelAnalysisLoader\Updated LLI Files\ALL WO Status.xlsx]
(ooxml, embedded labels, table is [10 WO Sales status]);"

[Taoufiq_Zarra]

Hi,

on behalf of the great @sunny_talwar (MVP),  I propose to you the solution he proposed:

Table:
LOAD *,
	 If([Order Number] = Previous([Order Number]), RangeSum(Peek('StatusNum'), 1), 1) as StatusNum;

LOAD * INLINE [
    Order Number, Status, Date
    1, 10, 12/1/2019
    1, 20, 12/2/2019
    1, 30, 12/2/2019
    1, 40, 12/3/2019
    1, 50, 12/4/2019
    1, 60, 12/6/2019
    1, 70, 12/6/2019
    2, 10, 10/1/2019
    2, 20, 10/2/2019
    2, 30, 10/10/2019
    2, 40, 10/12/2019
    2, 50, 10/14/2019
    2, 60, 10/16/2019
    2, 70, 10/16/2019
];

FinalTable:
LOAD Distinct [Order Number]
Resident Table;

FOR i = 1 to FieldValueCount('StatusNum')
	
	LET vField = FieldValue('StatusNum', $(i));
	
	Left Join (FinalTable)
	LOAD [Order Number],
		 Date as [Status $(vField)]
	Resident Table
	Where StatusNum = $(vField);
	
NEXT i;

DROP Table Table;

the result:

Cheers,

[nburton78]

this is very close, however what I didn't mention and probably should have mentioned is that an order may not go through all the statuses.  for example, 

1	10	1/1/2019
1	20	1/2/2019
1	30	1/3/2019
1	35	1/4/2019
1	40	1/5/2019
1	45	1/6/2019
1	50	1/7/2019
1	60	1/8/2019
1	70	1/9/2019
2	10	2/2/2019
2	30	2/3/2019
2	40	2/4/2019
2	45	2/5/2019
2	60	2/6/2019
2	90	2/7/2019
2	95	2/8/2019
3	10	3/3/2019
3	15	3/4/2019
3	20	3/5/2019
3	40	3/6/2019
3	45	3/7/2019
3	90	3/8/2019

so the data would look like this:

	10	15	20	30	35	40	45	50	60	70	90
1	1/1/2019		1/2/2019	1/3/2019	1/4/2019	1/5/2019	1/6/2019	1/7/2019	1/8/2019	1/9/2019
2	2/2/2019			2/3/2019		2/4/2019	2/5/2019		2/6/2019	2/7/2019	2/8/2019
3	3/3/2019	3/4/2019	3/5/2019			3/6/2019	3/7/2019				3/8/2019

Also, the name of the status is somewhat important.  

[Taoufiq_Zarra · Accepted solution]

Generic load will be helpful in this case

InputTable:
LOAD * INLINE [
  Order Number, Status, Date
    1, 10, 1/1/2019
    1, 20, 1/2/2019
    1, 30, 1/3/2019
    1, 35, 1/4/2019
    1, 40, 1/5/2019
    1, 45, 1/6/2019
    1, 50, 1/7/2019
    1, 60, 1/8/2019
    1, 70, 1/9/2019
    2, 10, 2/2/2019
    2, 30, 2/3/2019
    2, 40, 2/4/2019
    2, 45, 2/5/2019
    2, 60, 2/6/2019
    2, 90, 2/7/2019
    2, 95, 2/8/2019
    3, 10, 3/3/2019
    3, 15, 3/4/2019
    3, 20, 3/5/2019
    3, 40, 3/6/2019
    3, 45, 3/7/2019
    3, 90, 3/8/2019
];


GenTable:
Generic Load [Order Number], Status, Date Resident InputTable;

ResultTable:
LOAD Distinct [Order Number] Resident InputTable;

FOR i = 0 to NoOfTables()
  TableList:
  LOAD TableName($(i)) as Tablename AUTOGENERATE 1
  WHERE WildMatch(TableName($(i)), 'GenTable.*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
  LET vTable = FieldValue('Tablename', $(i));
  LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
NEXT i

Regards,

[nburton78]

Thank you that is Perfect!