Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
Thank you, Ralf
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
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,
The same thing is done with two lines of QlikView script code (LOAD, STORE), see above... 😉
Thanks to the power of QlikView!
WoW , Great !! ^^
Superb Ralf. Great solution.
Regards,
Jagan.