/* Qlik Sense data scrambler by yyg@qlik.com Ver 1.0 developed and tested with Qlik Sense 2.1.1 */ SUB Scramble(p_tablefield) ; //this semi-column is theoretically not necessary. However the value of parameter p_tablefield can be passed on without this semi-column. I think it's a bug. //the location of configuration file IF index(p_tablefield,':')=0 then //when parameter value doesn't have ':' which is necessary for specifiying file path LET v_tablefield = 'lib://Binary/'&'$(p_tablefield)'; Else //when file path is specified under legacy mode LET v_tablefield = '$(p_tablefield)'; ENDIF //retrieve the list of tables that need to be scrambled scramble_table_list: LOAD distinct scramble_table FROM $(v_tablefield) (ooxml, embedded labels, table is scramble); //retrieve the list of fields that need to be scrambled scramble_field_list: LOAD distinct scramble_field FROM $(v_tablefield) (ooxml, embedded labels, table is scramble); //rename the name of fields that need to be scrambled FOR i = 1 to NoOfRows('scramble_field_list') let v_scramble_field='['&FieldValue('scramble_field',$(i))&']'; let v_scramble_field_tmp='['&FieldValue('scramble_field',$(i))&'_tmp]'; rename field $(v_scramble_field) to $(v_scramble_field_tmp); NEXT i SET i=; //outter iteration will go through each table that has fields need to be scrambled FOR i = 1 to NoOfRows('scramble_table_list') let v_scramble_table_value=chr(39)&FieldValue('scramble_table',$(i))&chr(39); let v_scramble_table='['&FieldValue('scramble_table',$(i))&']'; let v_scramble_table_tmp='['&FieldValue('scramble_table',$(i))&'_tmp]'; let v_scramble_table_final='['&FieldValue('scramble_table',$(i))&'_final]'; //retrieve all the fields for one particular table scramble_field_onetable: LOAD distinct scramble_field as scramble_field_tmp FROM $(v_tablefield) (ooxml, embedded labels, table is scramble) where scramble_table= $(v_scramble_table_value); let v_scramble_load= v_scramble_table_final & ': load '; //inner iteration will go through all the fileds in one talbe and compose the load script for scrambling them FOR j = 1 to NoOfRows('scramble_field_onetable') let v_scramble_load= v_scramble_load& chr(39)&FieldValue('scramble_field_tmp',$(j))&'_'&chr(39)&'&Autonumber(['&FieldValue('scramble_field_tmp',$(j))&'_tmp]) as '&'['&FieldValue('scramble_field_tmp',$(j))&'], '; NEXT j let v_scramble_load = v_scramble_load & '* resident '& v_scramble_table; //execute the load script; $(v_scramble_load); SET j=; //drop the original table to avoid synthetic keys drop table scramble_field_onetable; drop table $(v_scramble_table); NEXT i SET i=; //drop original fileds FOR i = 1 to NoOfRows('scramble_field_list') let v_scramble_field_tmp='['&FieldValue('scramble_field',$(i))&'_tmp]'; drop field $(v_scramble_field_tmp); NEXT i SET i=; drop table scramble_table_list; drop table scramble_field_list; End SUB;