topic Re: Load data from excel to fill Null fields in Qlik in App Development

Load data from excel to fill Null fields in Qlik

Markbhai — Fri, 26 Apr 2024 20:47:14 GMT

I am building a table of Objectives and Controls - I have all of the Controls in Qlik but some have missing Objective and Objective Description fields

I also want to add Objective Number and Control Number which are new fields.

I have created a spreadsheet, to upload the missing data and the new fileds.

I am attempting LEFT JOIN in the load script, but none of the data is loading and I have no errors.

Thanks

LEFT JOIN ([tech_organizational_main_items]) LOAD

[id] AS [control.id],

[parent_id] as [main_items.id],

[category_id] as [category.id],

[objective_id] as [objective.id],

[control_name] AS [Control],

[control_description] AS [Control Description];

SELECT id,

`parent_id`,

`category_id`,

`objective_id`,

`control_name`,

`control_description`

FROM `db`.`tech_organizational_controls`;

LEFT JOIN ([tech_organizational_main_items]) LOAD

[id] AS [objective.id],

[obj_name] AS [Objective],

[obj_description] AS [Objective Description];

SELECT id,

`parent_id`,

`obj_name`,

`obj_description`

FROM `db`.`tech_organizational_objective`;

LEFT JOIN ([tech_organizational_main_items]) LOAD

[control.id] as [control.id],

[Category Number],

[objective.id],

[Objective],

[Objective Description],

[Control Number]

FROM [lib://Live - DB:DataFiles/ISO 27001_2022 Category Number Mapping - Qlik.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Re: Load data from excel to fill Null fields in Qlik

igoralcantara — Fri, 26 Apr 2024 21:38:52 GMT

Your script has 3 tables that are Left Join another table. One of those is causing the issue. Possibly because there are no matches between one of these LEFT joins and final table being load.

Try this first:

Comment all the 3rd table, reload, check the data. If there is data, you found your issue. If not, keep the 3rd table out and comment the 2nd. Do the same check. If still no data, do that with the first one. If still no data, tech_organizational_main_items is the problem.

When finding the table that is causing the problem. Load that table and tech_organizational_main_items, but comment the left join and exam the subset ration for they key fields between those two. They should be, for both, close or equals to 100%. If the sum of them is 100% or some other strange behavior, you found the problem. Thje tables key fields do not match. Maybe is a data format problem.

Re: Load data from excel to fill Null fields in Qlik

Markbhai — Fri, 26 Apr 2024 22:39:02 GMT

Update:

I have managed to fill the new fields, which were previously empty by changing the Left Join to

LEFT JOIN ([tech_organizational_main_items]) LOAD

[control.id] as [control.id],

[Category Number],

[Control Number]

FROM [lib://Live - DB:DataFiles/ISO 27001_2022 Category Number Mapping - Qlik.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Before this none would load.

Is there a way to add data to the existing Null fields?

Thanks

Re: Load data from excel to fill Null fields in Qlik

BrunPierre — Sat, 27 Apr 2024 11:56:34 GMT

Without sample data and a preview of your anticipated output, this is purely speculative.

Map_Spreadsheet: Mapping LOAD "objective.id", Objective &'|'& "Objective Description" &'|'& "Objective Number" &'|'& "Control Number" FROM ... csv; tech_organizational_main_itemsTmp: FROM `FROM `db`.`tech_organizational_main_items`; Left Join(tech_organizational_main_itemsTmp) LOAD * `FROM `db`.`tech_organizational_controls`; Left Join(tech_organizational_main_itemsTmp) LOAD * `db`.`tech_organizational_objective`; tech_organizational_main_items: LOAD *, If(Len(Trim(Objective))=0,SubField(ApplyMap('Map_Spreadsheet',"objective.id"),'|',1), Objective) as Objective1, If(Len(Trim("Objective Description"))=0,SubField(ApplyMap('Map_Spreadsheet',"objective.id","Objective Description"),'|',2), "Objective Description") as "Objective Description1", SubField(ApplyMap('Map_Spreadsheet',"objective.id"),'|',3) as "Objective Number", SubField(ApplyMap('Map_Spreadsheet',"objective.id"),'|',4) as "Control Number" Resident [tech_organizational_main_items]; DROP Table tech_organizational_main_itemsTmp; DROP fields Objective, Objective Description"; RENAME Fields Objective1 to Objective, Objective Description1 to "Objective Description";

Re: Load data from excel to fill Null fields in Qlik

Markbhai — Mon, 29 Apr 2024 07:35:43 GMT

Thanks everyone.

In the end I gave up and created and joined the sheet as its own table. not what I wanted, but given the time it was taking I elected to go for this less elegant solution.

Thank you to everyone for their input.

Mark