Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
vadimbasalaj
New 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!

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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

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

6 Replies
MVP
MVP

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

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;

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

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

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

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

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

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!

MVP
MVP

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

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

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

Yes, it works for me! Thank you!