topic Re: How can I Replace '-' with Zero quickly or efficiently ? in App Development

How can I Replace '-' with Zero quickly or efficiently ?

SRT10 — Mon, 06 Dec 2021 21:12:34 GMT

I want to replace field values with Zero if value is hyphen i.e. '-'.

Have tried many things like

If Statement
Replace Function
Pick Match
Applymap

But all of these are taking lot of time to replace existing values to 0. Can I do this quickly in someway ?

Data volume is ~274,444,124

Script Example :

FinalData:
NoConcatenate
LOAD *, if(Rate='-','0',Rate) As [Rate_New] Resident Final;
Drop Table Final;
Rename table FinalData to Final;

Above block itself is taking excessive time. Around 2 to 2.5 hours

Drop field [Rate_New] from FinalData;
Rename Field [Rate_New] to [Rate];

Re: How can I Replace '-' with Zero quickly or efficiently ?

Or — Mon, 06 Dec 2021 22:01:01 GMT

Assuming Rate is always either numeric or a hyphen (I'm assuming it is in fact a hyphen rather than null), you could use alt(Rate, 0). I'd suggest doing this, or whatever other solution you use, in the original load - there's no reason to make a second pass just to do this.However, no matter which function you use, it won't significantly change the loading time unless you're specifically working with an optimized (QVD) load. If that's the case, I'd suggest replacing the values before they go into the QVD rather than doing it after the fact.

Unrelated, it's quite unusual that it'd take so long for Qlik to read through a few hundred million lines. Unless the lines themselves are very long (insofar as how much data each one contains), this seems like something that should take no more than a few minutes.

Re: How can I Replace '-' with Zero quickly or efficiently ?

SRT10 — Mon, 06 Dec 2021 22:20:45 GMT

@Or
There are around 128 fields for each record entry.

Re: How can I Replace '-' with Zero quickly or efficiently ?

Or — Mon, 06 Dec 2021 22:24:43 GMT

Presumably, what is taking the time is reading all of the lines. This one formula probably doesn't have any particular impact on the total time. You can test this by reading all of the lines both with and without the formula, or even by loading a subset of the lines, and timing the difference.

As I said, your best bet is to do this on the first pass of reading your data, without adding a second load just for this one action.

Re: How can I Replace '-' with Zero quickly or efficiently ?

nfederico360insights — Mon, 06 Dec 2021 22:26:42 GMT

Hi,
I would leverage the optimized load and Exists statements.. with something like:

exclude_filter: Load EmptyRate Inline [ EmptyRate - 0 ] ; FinalData: NoConcatenate LOAD *, '0' As [Rate_New] Resident Final where Exists(EmptyRate,Rate) ; Concatenate(FinalData) LOAD *, Rate As [Rate_New] Resident Final where not(Exists(EmptyRate,Rate)) ; Drop Table Final; Rename table FinalData to Final; Drop Table exclude_filter; Drop field [Rate] from Final; Rename Field [Rate_New] to [Rate];

Re: How can I Replace '-' with Zero quickly or efficiently ?

MayilVahanan — Tue, 07 Dec 2021 01:55:47 GMT

Try like below

SET NULLINTERPRET='-';
NullAsValue *;
Set NullValue = 0;

Load * Inline
[
A, B, C
10, -, 1
5, 4, -
-, 6, 7
];

O/P:

if its comes from ODBC, instead of NULLINTERPRET, replace with NULLDISPLAY.

SET NULLDISPLAY=<sym>;

Re: How can I Replace '-' with Zero quickly or efficiently ?

SRT10 — Tue, 07 Dec 2021 05:44:06 GMT

This looks like at front end or how we can display. I need this in qlikview memory table. needs to generate csv based on this data.

I am reading data from a qvd.

Re: How can I Replace '-' with Zero quickly or efficiently ?

Or — Tue, 07 Dec 2021 08:12:48 GMT

Then I'd return to my original suggestion - if possible, edit the data before it gets into the QVD, not while reading it. Literally anything that modifies the data will prevent an optimized reload from QVD, and I can't think of a solution for this with just renaming fields and a simple Exists() statement, which is what you're allowed to use and maintain optimized load (see e.g. https://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/ )

Possibly you could make this go faster by loading a partial set of data rather than load *, e.g.

Table1:

Load * From QVDFile;

JOIN Load UniqueID, alt(Rate,0) as Rate2

Resident Table1;

Drop Field Rate;

Rename Field Rate2 to Rate;

I'm not sure if that would go any faster, but might be worth testing.