Deploying identical transformations to series of fields within a load statement

[lwestmaas]

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?

[rwunderlich · Accepted solution]

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

[lwestmaas]

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;