topic Re: Deploying identical transformations to series of fields within a load statement in App Development

Deploying identical transformations to series of fields within a load statement

lwestmaas — Thu, 27 May 2021 14:23:39 GMT

Hello,

I have a series of fields that are transformed multiple times, always in identical ways, over the course of a load script. Some transformations simple, some a little more complex, but always identical. For example, the first one is a simple sum, similar to this:

load
id,
sum(gears_thismonth) as gears_todate,
sum(widgets_thismonth) as widgets_todate
resident gearsandwidgets
group by id;

There are currently about 15 fields undergoing these transformations, and it's possible I'll add more. Editing some of the transformations is tedious and error-prone.

I've looked into using a loop directly within a load statement and calling a subroutine within a load statement (not as good as using a loop but would still reduce the amount of copy-pasting necessary) and it looks like neither are possible.

The closest I can think is to define the bulk of the script block as a variable - something like:

let transformations = '';
for each field in 'gears', 'widgets'
let transformations = '$(transformations)' & ',' &
'sum($(field)_thismonth) as $(field)_todate'
;
next
load
id
$(trasformations)
resident gearsandwidgets
group by id;

(Haven't tested this solution yet.)

Has anyone else run into this issue and come up with a sustainable solution?

Re: Deploying identical transformations to series of fields within a load statement

rwunderlich — Thu, 27 May 2021 17:28:09 GMT

I think you are on the right track with the variable approach. I might consider building with a LOAD statement instead and then transferring into variables. Like this:

Set vNewline = ',' & chr(10);
Transforms:
LOAD
Concat(
'sum([' & infield & '_thismonth]) as [' & infield & '_todate]'
, $(vNewline)
) as Transform_Todate,
Concat(
'Avg([' & infield & '_count]) as [' & infield & '_Average_Count]'
, $(vNewline)
) as Transform_AvgCount
;
LOAD * Inline [
infield
gears
widgets
]
;
Let vTransformTodate = peek('Transform_Todate');
Let vTransformAvgCount = peek('Transform_AvgCount');

Data:
LOAD
$(vTransformTodate),
$(vTransformAvgCount)
Resident gearsandwidgets;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Deploying identical transformations to series of fields within a load statement

lwestmaas — Thu, 27 May 2021 19:21:23 GMT

Thanks! This was a good push, and it seems to be working perfectly. It lets me centralize creation of each function in a single location, and for readability I like using the inline table to list out the fields over putting them in a loop.

Full sample pasted below. Note the third function "transform_complexrecords_todate" allows use of a loop (with variable "level") when actually called in my script. And I added another twist - a loop that autocreates each variable (starting with "for adds_and_transforms = 1" at bottom).

Set newline = ',' & chr(10); adds_and_transforms: LOAD concat('sum('&infield&'_inmonth_store) as '&infield&'_todate_store',$(newline)) as transform_simplerecords_todate_store, concat(infield&'_inmonth_store',$(newline)) as add_simplerecords_inmonth_store, concat( 'if(previous(%key_'&chr(36)&'(level)_year)=%key_'&chr(36)&'(level)_year, peek(' & infield & '_todate_'&chr(36)&'(level)) + ' & infield & '_inmonth_store, ' & infield & '_inmonth_store ) as ' & infield & '_todate_'&chr(36)&'(level)' , $(newline) ) as transform_complexrecords_todate ; LOAD * Inline [ infield gears widgets ] ; for adds_and_transforms = 1 to NoOfFields('adds_and_transforms') let add_or_transform_name = fieldname($(adds_and_transforms),'adds_and_transforms'); let $(add_or_transform_name) = peek('$(add_or_transform_name)'); next let adds_and_transforms = ; let add_or_transform_name = ; drop table adds_and_transforms;