topic subfield() - it's fine, but it's TOO SLOWLY!!! in QlikView

subfield() - it's fine, but it's TOO SLOWLY!!!

Sat, 16 Aug 2014 06:31:50 GMT

Hello

I have table with 2 columns: ContractNumber and VALUE

Field VALUE like this "11231,432423,43545,565465,456456"

ContractNumber like this 2432423/3234

I need to separate values in VALUE, when I try use subfield() - it's fine, but it's TOO SLOWLY!!! Then I try to use load with separator ',' - it's great, it's works fast, but I can't save ContractNumber in this case ;(((

I need help ... please

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

ecolomer — Sat, 16 Aug 2014 06:59:12 GMT

can you try with left and right funtions

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

Sat, 16 Aug 2014 07:41:48 GMT

How?

Here is I load separated values of VALUE:

LOAD

@1 as value

FROM

ContractByLinks.txt

(txt, no labels, delimiter is ',', no quotes);

join by which field? with... what?

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

ecolomer — Sat, 16 Aug 2014 08:06:35 GMT

Send my a file to try

Re: Re: subfield() - it's fine, but it's TOO SLOWLY!!!

Sat, 16 Aug 2014 08:42:32 GMT

thanks for try

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

jagan — Sat, 16 Aug 2014 09:02:46 GMT

Hi,

I think SubField() is the right option in this case eventhough it is slow. How many records you have in the table? If you have more records then it will definitely take time to load, but you will correct correct data.

Regards,

Jagan.

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

ecolomer — Sat, 16 Aug 2014 09:12:03 GMT

I agree with Jagan, Subfield es the best solution

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

Sat, 16 Aug 2014 13:03:22 GMT

I have more than 30 000 000 records . When I try to load data using subfield() - I think the load will never finish, it's works more then 24 hours. I really can't use SubField() function.

Second problem: I need to reload my data every day, so I can't take more than 24 hours for my script.

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

Sat, 16 Aug 2014 13:08:21 GMT

Not for me

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

ecolomer — Sat, 16 Aug 2014 15:16:34 GMT

This 30M records you read all days?

And this recordas are diferente each day?

if NO, other solution is to save the records converted in a QVD file .... you know it?

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

rbecher — Sat, 16 Aug 2014 17:40:33 GMT

Hi Gertye,

I have an idea to solve this but I would need the file with all columns (ContractNumber is missing) to test it..

- Ralf

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

ashfaq_haseeb — Sun, 17 Aug 2014 06:10:49 GMT

Hi,

I too agree subfield is the best option here.

Is your database indexed? if not try by adding index to your table.

Why don't you try to store raw data as it is in qvd first and then later apply transformations on qvd.

By this at least you will come to know where is the issue coming from.

Regards

ASHFAQ.

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

rbecher — Sun, 17 Aug 2014 10:22:13 GMT

Hi Gertye,

you could replace the delimiter with a line break, store it in a file and read again:

ContractByLinksTmp:

LOAD RecNo() as ContractNumber, replace(VALUE, ',', chr(13) & chr(10) & RecNo() & chr(9)) as VALUE

FROM

[ContractByLinks.txt]

(txt, utf8, embedded labels, delimiter is '\t', msq);

STORE ContractByLinksTmp INTO ContractByLinksTmp.txt (txt, delimiter is '\t');

Drop Table ContractByLinksTmp;

LOAD ContractNumber, replace(VALUE, '"', '') as VALUE

FROM

(txt, utf8, embedded labels, delimiter is '\t', no quotes);

STORE ContractByLinksTmp INTO ContractByLinksOut.txt (txt, delimiter is '\t');

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

I used RecNo to simulate the ContractNumber (missing in file).

- Ralf

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

Sun, 17 Aug 2014 10:40:38 GMT

Thank you, Ralf

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

Sun, 17 Aug 2014 14:24:24 GMT

Hi,

I think you have to transform the data first, Then load it to QlikView.

So I made a Python program to separate your file. But it is my first Python program, So it is not well done, but you can achieve you want to.

To execute the program, Install Python 3.4(you can download from internet).

I hope this program is useful.

#-------------------------------------------------------

# Python Test Program

# Objective : Separate a long record with delimiter.

# Environment : Python 3.4

# ------------------------------------------------------

# File Name : ContractByLinks.py

# Delimiter : ','

# Remove : '"'

# Input File : Text file including ',' and '"'.

# Output File : A result with separated columns.

# This is my first program for Python. So not well done.

# But this can resolve you want to.

#-------------------------------------------------------

StringC = [""]

j = 0

k = 0

recNum = 0

f = open("d:\working\ContractByLinks.txt", 'r') # Input File

o = open("d:\working\ContractByLinks.out", 'w') # Output File

lines = f.readline() # Ignore the first line(Column Name)

while True :

lines = f.readline() # Read next line from the input file

if not lines: # If EOF, finish the program

break

k = 1 # ignore the first character of each line which include '"'

recNum = recNum + 1 # Total record number

l = len(lines) # Record length

StringC = "" # Initialize a variable

for j in range(l): # Performed by repeating as many as record length.

if lines == ',':

o.write(str(StringC))

o.write('\n')

StringC = ""

k = k + 1

if lines == '\n':

o.write(str (StringC))

o.write('\n')

StringC = ""

k = 1

if lines == '"':

o.write(str(StringC))

o.write('\n')

StringC = ""

k = 1

else:

StringC += str(lines)

k = k + 1

k = 0

f.close()

o.close()

print ('Record # %d \n' % recNum )

print("-------------------- FINISHED --------------------")

WanKi,

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

rbecher — Sun, 17 Aug 2014 14:35:11 GMT

The same thing is done with two lines of QlikView script code (LOAD, STORE), see above... 😉

Thanks to the power of QlikView!

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

Sun, 17 Aug 2014 14:52:38 GMT

WoW , Great !! ^^

Re: subfield() - it's fine, but it's TOO SLOWLY!!!

jagan — Mon, 18 Aug 2014 01:27:33 GMT

Superb Ralf. Great solution.

Regards,

Jagan.