Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Can you please recommend a fastest (performance wise) solution on how to deal with the following.
I have this table with empty/null fileds - please see below - but I need that instead of those empty fields/nulls in Price column, the first Price column was filled in with the Price Value of the first Price Value till the next Price Value. Then after the next Price Value it was filled with the next Price Value til the next next Price Value etc.....
So it would look like this:
Time Price
03:16:56 4604
03:16:56 4603.75
03:16:57 4603.75
03:17:01 4603.75
.......
03:17:10 4604
03:17:13 4604
03:17:26 4603.75
03:17:30 4603.75
.......
etc.....
Any idea? Thanks in advance.
kind regards
r.
P.S. there is no any logic /patterns on how the Price changes.....
This should fill in prices in your sample csv:
SET ThousandSep=',';
SET DecimalSep='.';
T1:
LOAD Ask_price,
Ask_size,
Bid_price,
Bid_size,
date,
Price,
Quantity,
time,
Volume_size
FROM
[sample (1).txt]
(txt, utf8, embedded labels, delimiter is ';', msq);
LOAD recno() as RecNo,
date,
time,
if(len(trim(Price)),Price,Peek(Price)) as Price,
Quantity
RESIDENT T1
ORDER BY date, time;
DROP TABLE T1;
T1:
LOAD time, price, quantity
FROM YourTable;
RESULT:
NOCONCATENATE
LOAD time,
alt(price, Peek(price) ) as price,
quantity
RESIDENT T1
ORDER BY time;
DROP TABLE T1;
edit: changed previous() to peek()
Are you trying to do this in an expression or back end of the script?
@SunnyT I would prefere it to be done in backend / the script editor if its possible (when reloading the script once). Basically I got a table from MS SQL DB.
mytable:
SQL SELECT "Price",
date,
"Volume_size"
FROM "my_db".dbo."my_table";
Then I think you should be able to do it using swuehl's provided script. If it did not work, then provide a sample (preferably in an Excel file) to help you better.
Try something like
mytable:
LOAD Alt( Price, Peek( Price) ) as Price,
date.
[Volume_size];
SQL SELECT "Price",
date,
"Volume_size"
FROM "my_db".dbo."my_table"
ORDER BY date ASC;
Hi, I tryed the script it did solved but partially. i am still having blanks. I am attaching the main table - got exported into csv.
I made a straight table with dimentions time, price and expresion sum(Quantity). After using swuel script it looks like this.
This blog post Generating Missing Data In QlikView by HIC is well worth a read. Not just re your question, but also other related scenarios.
This should fill in prices in your sample csv:
SET ThousandSep=',';
SET DecimalSep='.';
T1:
LOAD Ask_price,
Ask_size,
Bid_price,
Bid_size,
date,
Price,
Quantity,
time,
Volume_size
FROM
[sample (1).txt]
(txt, utf8, embedded labels, delimiter is ';', msq);
LOAD recno() as RecNo,
date,
time,
if(len(trim(Price)),Price,Peek(Price)) as Price,
Quantity
RESIDENT T1
ORDER BY date, time;
DROP TABLE T1;