Excel two levels (transaction info and transaction details) into one list

[vadimbasalaj]

Hi, Community,

please help to load the data as exported from ERP (there is a list of transactions, where the first row refers to transaction No, date, etc, followed by a detailed list of SKUs for the transaction):

into a list like this:

Thank you!

[maxgro]

look at the attachment

RESULT

SCRIPT

Directory;

XLS:

LOAD

rowno() as Id,

A,

    B,

    C,

    D,

    E,

    WildMatch(A, 'trans*') as FlagTrans

FROM

example.xlsx

(ooxml, no labels, table is [ERP export])

Where len(trim(B)) >0;

XLS2:

load

*,

if(FlagTrans,

A,

if(Peek('FlagTrans'), Peek('A'), Peek('TransactionId'))

) as TransactionId

Resident XLS

Order By Id;

DROP Table XLS;

Left Join (XLS2)

LOAD

TransactionId,

B as TransactionDate,

C as TransactionC,

D as TransactionD,

E as TransactionE

Resident XLS2

Where FlagTrans;

tmpRK:

Right Keep (XLS2)

LOAD

Id

Resident XLS2

where not FlagTrans;

DROP Table tmpRK;

[tamilarasu]

Hi Vadims,

You can also try,

Data:
LOAD If(Len(Trim(C))>0,A,Peek('ColumnA')) as ColumnA,
     Date(If(Len(Trim(C))>0,B,Peek('ColumnB'))) as ColumnB,
     If(Len(Trim(C))>0,C,Peek('ColumnC')) as ColumnC,
     If(Len(Trim(C))>0,D,Peek('ColumnD')) as ColumnD,
     If(Len(Trim(C))>0,E,Peek('ColumnE')) as ColumnE,
     A,
     B, 
     D,
     E
    FROM
[example (3).xlsx]
(ooxml, no labels, table is [ERP export]);

NoConcatenate
Result:
Load *
Resident Data Where Len(Trim(A))>0 and Not WildMatch(A,'tra*');

DROP Table Data;

Output:

[vadimbasalaj]

Thank you, Massimo!  It works!

However, there is a problem: I gave names like transactionNo1, transactionNo2, etc.  for better representation of the example, but in real data export those are just numbers, therefore, unfortunately, WildMatch(A, 'trans*' can not be used.

I would appreciate your help to solve this.

Thank you!

[vadimbasalaj]

Thank you, Tamil!  It works!

However, there is a problem: I gave names like transactionNo1, transactionNo2, etc.  for better representation of the example, but in real data export those are just numbers, therefore, unfortunately, Not WildMatch(A,'tra*') can not be used.

I would appreciate your help to solve this.

Thank you!

[maxgro · Accepted solution]

I replaced the wildmatch with

IsNum(A) as FlagTrans

this is the modified excel (transactions are numbers)

and this is the result

[vadimbasalaj]

Yes, it works for me! Thank you!