Filtering out 0 values from CrossTable() function

[Stanislav1]

Hello everybody,

I have the following issue, which If I solve I will improve the time efficiency of my app with 30-40%. 

Now, I am using a CrossTable() function to seperate the metrics and their corresponding values.

For each metric where there is no value it also creates a row, which makes the size of the final table a problem.

I currently use the following code to remove the Null values, but it makes the transformation time inefficient, because it loads the 0 values and then after that filter them out in the next step.

How can I do both of these steps, creating and filtering the CrossTable into one step. In essence, I want to load everything into CrossTable() but without the 0 values at once. Please check the attached file for the provided function. 

For your convenience, I will add the code here in the chat as well:

googleplay.ads_temp: CrossTable('metric','value', 16)

LOAD

[#eventDate]

, [#installDate]

    , Source

    , event_time

    , install_time

    , event_type

    , package_name

    , device

    , country_code

    , isOrganic

    , ad_network

    , campaign

    , ad_group

    , ad_name

    , App

    , product_line

    , ads_cost, impressions, clicks

Resident

googleplay.ads_group;

drop table googleplay.ads_group;

googleplay.ads: NoConcatenate

LOAD *

RESIDENT googleplay.ads_temp

WHERE value <> 0; 

I will highly appreciate your ideas and solutions. If you need any further details, please feel free to reach me out. Thank you in advance. 

[KalmerKaljuste-BeTerna]

Since we don't know what kind of values are in the "value" field (could be text, integer etc), I will assume that you want to get rid of NULL values (not 0 values).
Try: Where NOT isnull(value);

[Stanislav1]

Forgive me for not clarifying. The values in the 'value' field are integers. I already tried filtering out the value column in the CrossTable() creation function, but without success, since CrossTable() is a transforming prefix and it is not a complete 'Load' or 'Resident'. I tried where NOT isnull(value) as well. 

Maybe possible solution could be to use 'Preceding Load' instead of using Resident in this case. However, I am not sure if this is possible with the CrossTable() function. Could you please elaborate on this proposition if it is possible or not? Thank you in advance. @KalmerKaljuste-BeTerna 

[marcus_sommer · Accepted solution]

You could try to replace the ZERO with NULL, maybe with something:

...
if(ads_cost = 0, null(), ads_cost) as ads_cost
...

[Stanislav1]

@marcus_sommer And what should I do next? Filter out the non null() values? If that is what you are referring, it doesn't work.

[marcus_sommer]

I'm not sure about it but if I remember correctly the NULL is while the crosstable-resolving skipped and needs no extra filtering. Just give it a try.

[KalmerKaljuste-BeTerna]

I generated some dummy data and used your code. It seems to be working exactly as you want (I did it using QV as there is no more QS desktop available anymore).

googleplay.ads_group:
Load * Inline [
eventDate, installDate, Source, event_time, install_time, event_type, package_name, device, country_code, isOrganic, ad_network, campaign, ad_group, ad_name, App, product_line, ads_cost, impressions, clicks
09.09.2024, 04.09.2024, ABC, 15:15:00, 10:15:00, EventTypeABC, PackageNameABC, 32141, EST, Yes, 31512, 432532, 525324, AD_Name_ABC, 5235238432, 4325946734, 666, 0, 100400
];

googleplay.ads_temp:
CrossTable('metric','value', 16)
LOAD
eventDate
,installDate
,Source
,event_time
,install_time
,event_type
,package_name
,device
,country_code
,isOrganic
,ad_network
,campaign
,ad_group
,ad_name
,App
,product_line
,ads_cost, impressions, clicks
Resident
googleplay.ads_group;

drop table googleplay.ads_group;

googleplay.ads: NoConcatenate
LOAD *
RESIDENT googleplay.ads_temp
WHERE value <> 0;

drop table googleplay.ads_temp;

[Stanislav1]

Yes, I know it is working as intended. However, I want to do the filtering for the value <> 0 in the CrossTable(). This way I will save a lot of time for the transformation, because I don't have to load the table second time using resident for filtering out the 0 values. Do you have any propositions how I can do that? @KalmerKaljuste-BeTerna 

[marcus_sommer]

You could add a filter which filters the data against the load which is only applicable if a complete record has no valid values but not against the crosstable-transforming. In your case this might be something like:

where rangesum(ads_cost, impressions, clicks);

Beside this you may to consider to skip the inbuilt crosstable-feature and loading the data manually which is especially simple if there are only a few not changing fields to transpose which might be even done within a loop, for example:

for each f in 'ads_cost, impressions, clicks'
  t: load F1, F2, F3, '$(f)' as metric, $(f) as value from X where $(f);
next

I would expect that such approach runs significantly faster as the crosstable-statement.

[KalmerKaljuste-BeTerna]

I assume that you have huge amount of data. As I understand most likely the majority of the data is with 0 values, thus skipping them before crosstable is what you're trying to solve.
Having that mentioned, I agree with @marcus_sommer - whether you "loop" through the 3 fields which may contain 0 as a value and you skip them OR you load the data once, then you may create 3 flags which determine whether one of the fields 'ads_cost', 'impressions', 'clicks' was 0. Now you load multiple tables using NoConcatenate without the field where the flag = 1 (when the value is 0). You perform the crosstable function for all possible combinations of possible fields.
I would build a SUB to call the cross table again with a variable of the existing fieldnames that you want to transform.
Please note that these steps might take as much time as dropping the 0's in the end.