Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vadimbasalaj
Contributor
Contributor

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

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):

exported.JPG

into a list like this:

needed.JPG

Thank you!

1 Solution

Accepted Solutions
maxgro
MVP
MVP

I replaced the wildmatch with

IsNum(A) as FlagTrans

this is the modified excel (transactions are numbers)

1xls.png

and this is the result

1.png

View solution in original post

6 Replies
maxgro
MVP
MVP

look at the attachment

RESULT

1.png

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
Champion
Champion

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:

Capture.PNG

vadimbasalaj
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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
MVP
MVP

I replaced the wildmatch with

IsNum(A) as FlagTrans

this is the modified excel (transactions are numbers)

1xls.png

and this is the result

1.png

vadimbasalaj
Contributor
Contributor
Author

Yes, it works for me! Thank you!