I need to know how to separate information from 1 row in 2 columns. The report download by SAP has this format. So I need to get 2 columns ID and Date from all information that coming from SAP. I put an Excel example.
There are probably some different approached to solve your problem, e.g. something like this might help
INPUT:
LOAD * INLINE [
LINE
4504385984 ZPI 10077759 CAMINOS DEL SOL S A F25 27.09.2012
Product a
Product b
Product c
Product d
Product d
Product e
4504533448 ZPI 10077282 CLP GENERAL INDUSTRIES LTD F81 21.01.2011
Product a
Product b
Product c
Product d
Product e
Product f
Product g
Product h
Product i
4504533443 ZPI 10077282 CLP GENERAL INDUSTRIES LTD F81 21.01.2012
Product a
Product b
Product c
Product d
Product e
Product f
];
RESULT:
LOAD
subfield(LINE,' ',1) as ID,
right(LINE,10) as Date
Resident INPUT where isnum(left(LINE,10));
So you need a where clause with a condition that spearates your lines you want to parse from the product lines.
Maybe just check if the first ten characters are numerical.
Then you could parse your lines e.g. by using subfield and right() functions. The date will be correctly parsed if your date format is matching the input format.
