How to import a flat file with many instances

[Anonymous]

Hi,

I'm trying to import a metadata file for invoice creation.

There are two versions, one with only one invoice, and another with n invoices.

They both have in common that I have to transform them, and rotate them right to get the Fieldname / Data.

Layout Example without transform:

0HCDLNRE DE

0HCTTXDE Deutschland

0HCTTXRE Deutschland

0HDACUA1 asdf

0HDACUA3 gerg

0HDACUA4 Fsdf

Importing the files with only one invoice is quiet easy, but I am not able to import the files with many invoices.

Each new invoice begins with the following header:

BEGINOIS1990H

*TIME 20170818082841

*LANGUAGE DE

*ZZCONO 0

*ZZDIVI   

*ZZFACI 100

*ZZWHLO 100

*LAYOUT A4 

I would have to tell Qlikview to create a line feed before the next instance of "BEGINOIS1990H" begins I guess.

Does anyone have an idea on how to solve this task?

For testing I attached an example with a header and 2 invoiceheaders

Thanks in advance

[m_woolf]

Maybe something like:

Load

     Field

from MyFile

where left(Field,1)<> '*' and left(Field,5)<>'BEGIN';

[Anonymous]

I can't imagine how to adapt your suggestion to my task. Could you please give me an example what you mean?

[Anonymous · Accepted solution]

I played aroud with something like that, but thats not flexible enough because I don't know how many invoices the next file might contain.

LOAD [*SERVER],
     [*PORT],
     [*NAME],
     [*USER],
     [*JOBID],
     BEGINInvoice,
     [*TIME],
     [*LANGUAGE],
     [*ZZCONO],
     [*ZZDIVI],
     [*ZZFACI],
     [*ZZWHLO],
     [*LAYOUT],
     [0HCDLNRE],
     [0HDACUA3],
     [0HDACUA4],
     [0HDACUNM]
FROM
[Filepath:\TST Invoice]
(txt, codepage is 1252, embedded labels, delimiter is '\t', filters(
Transpose()
));


Concatenate
LOAD [*JOBID],
     BEGINInvoice1 as BEGINInvoice,
     [*TIME1] as [*TIME],
     [*LANGUAGE1] as [*LANGUAGE],
     [*ZZCONO1] as [*ZZCONO],
     [*ZZDIVI1] as [*ZZDIVI],
     [*ZZFACI1] as [*ZZFACI],
     [*ZZWHLO1] as [*ZZWHLO],
     [*LAYOUT1] as [*LAYOUT],
     [0HCDLNRE1] as [0HCDLNRE],
     [0HDACUA31] as [0HDACUA3],
     [0HDACUA41] as [0HDACUA4],
     [0HDACUNM1] as [0HDACUNM]
FROM
[Filepathll\TST Invoice]
(txt, codepage is 1252, embedded labels, delimiter is '\t', filters(
Transpose()
));

[sasiparupudi1]

What is the expected outpt for the attached sample?

[Anonymous]

The expected result should look like this.

Header duplicated, Entries in seperate rows.

*JOBID	*LANGUAGE	*LAYOUT	*NAME	*PORT	*SERVER	*TIME	*USER	*ZZCONO	*ZZDIVI	*ZZFACI	*ZZWHLO	0HCDLNRE	0HDACUA3	0HDACUA4	0HDACUNM
43120677479865374612312	DE	A4	Invoice	12378	TST	20170818082840	User	0		100	100	Country1	Street1	Town1	Name1
43120677479865374612312	DE	A4	Invoice	12378	TST	20170818082841	User	0		100	100	Country2	Street2	Town2	Name2

[MarcoWedel]

Hi,

another solution might be:

table1:
LOAD *, RangeSum(Peek(InvoiceNo),-(@1='BEGINInvoice')) as InvoiceNo
FROM [https://community.qlik.com/servlet/JiveServlet/download/1461208-320164/TST%20Invoice] (txt, codepage is 1252, no labels, delimiter is '\t', msq);

tabTemp1:
Generic
LOAD InvoiceNo as TempField,
    @1,
    @2
Resident table1
Where not InvoiceNo;

tabTemp2:
Generic
LOAD InvoiceNo,
    @1,
    @2
Resident table1
Where InvoiceNo;

DROP Table table1;

tabInvoice:
LOAD '' as TempField2 AutoGenerate 0;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'tabTemp?.*') THEN
    JOIN (tabInvoice) LOAD * RESIDENT [$(vTable)];
    DROP TABLE [$(vTable)];
  ENDIF
NEXT i

DROP Fields TempField, TempField2, BEGINInvoice;

hope this helps

regards

Marco

[Anonymous]

Hi Marco,

your solution seems very promissing.

Loading my first File is flooding my Ram because of the many fields and instances.

I'm not understanding what your approach exactly does, so could you please tell me if there is a possibility to "filter" some fields?

Like I don't want to load the "0HDACUA3" Field

[Peter_Cammaert]

Add a WHERE clause to the first LOAD in Marco's example, in which you state the values to skip in the first column.

Like

:

WHERE (@1 <> '0HDACUA3');

[Peter_Cammaert]

Multiple values can be skipped using a construct like:

:

WHERE Not Match(@1, '0HDACUA3', 'OtherValue', ...);