topic How to load two .txt documents in one table? in QlikView

How to load two .txt documents in one table?

tomcatxx — Wed, 25 Nov 2020 16:16:04 GMT

Hi there, I have 2 .txt and want to load them in one table.

1.txt (x is different values.)

C1	C2	C3	C4
x	x	x	x
x	x	x	x
x	x	x	x
x	x	x	x
x	x	x	x
x	x	x	x
x	x	x	x
x	x	x	x

2.txt

C5	C6	C7	C8
1	2	3	4

What I want

C1	C2	C3	C4	C5	C6	C7	C8
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	3	3	4

There are no same lab in 2 txt documents.

Thank you for ur help.

Re: How to load two .txt documents in one table?

Peter_Cammaert — Fri, 22 Jun 2018 09:56:35 GMT

You can just JOIN the two tables. For example:

Table:

LOAD * INLINE [

C1, C2, C3

1, 2, 3,

4, 5, 6

7, 8, 9

];

LEFT JOIN (Table)

LOAD * INLINE [

C7, C8, C9

10, 11, 12

];

But this is actually a cartesian product that does not exhibit its usual data explosion because your example is very limited. Cartesian Products must be tightly controlled, or they'll produce unwanted effects.

You're sure that you don't have a common field in those two tables for the JOIN to match rows?

Re: How to load two .txt documents in one table?

PrashantSangle — Fri, 22 Jun 2018 10:03:26 GMT

only join keyword will help you

Load * from table1;

Join

Load * from table2;

Regards,

Re: How to load two .txt documents in one table?

tomcatxx — Fri, 22 Jun 2018 11:05:45 GMT

It was a txt document.

looks like

Value1 Value2 Value3 Value4

Test 1 Test 2 Test 3

Nr kg N MM

1 x x x

2 x x x

3 x x x

4 x x x

5 x x x

6 x x x

It's a product test record. Value1 is date, Value2 is Tester, Value3 is worktime and Value4 is productID. kg, N and mm are units(I dont need load units). I have more than 1000 txt documents like that .

One example is:

20.06.2018 Tom Night 1001

Test 1 Test 2 Test 3

Nr kg N MM

1 x x x

2 x x x

3 x x x

4 x x x

5 x x x

6 x x x

I think it contains 2 parts.

So I loaded this txt document in 2 tables.

1.txt

21.06.2018 Tom Night 1001

2.txt

Nr Test 1 Test 2 Test 3

1 x x x

2 x x x

3 x x x

4 x x x

5 x x x

6 x x x

What I want is

Nr Test 1 Test 2 Test 3 Date Tester Worktime ProductID

1 x x x 21.06.2018 Tom Night 1001

2 x x x 21.06.2018 Tom Night 1001

3 x x x 21.06.2018 Tom Night 1001

4 x x x 21.06.2018 Tom Night 1001

5 x x x 21.06.2018 Tom Night 1001

6 x x x 21.06.2018 Tom Night 1001

But there are no same keyword in 1.txt and 2.txt.

-----------------------------------------------------------------------------------------------------------------------------

And then I will load all 1000 txt documents in one tablewith SUB ScanFolder(Root) .

Nr Test 1 Test 2 Test 3 Date Tester Worktime ProductID

...

Re: How to load two .txt documents in one table?

Peter_Cammaert — Fri, 22 Jun 2018 11:16:20 GMT

Maybe you can approach this in a different way.

Product ID is present in every original text file. You could translate the current text file split code into a FOR EACH loop that scans all text files and for every text file

parses the first line into 4 variables (vDate, vTester, vWorktime and vProductID)
LOADs all other lines (skip the first one) as a fixed format table and Autoconcatenate the fields together with the four variables into the target table. This LOAD will skip any row for which Field1 = 'Nr' (the units line)

That way, you can do all the work in one big sweep.

Peter

Re: How to load two .txt documents in one table?

tomcatxx — Fri, 22 Jun 2018 11:54:51 GMT

Thank you. It sounds good. But can u help me to write some script for these 2 files(above). I want to know it clearer.

C1	C2	C3	C4	C5	C6	C7	C8
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	3	3	4

C1	C2	C3	C4	C5	C6	C7	C8
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	3	3	4

C1	C2	C3	C4	C5	C6	C7	C8
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	2	3	4
x	x	x	x	1	3	3	4