topic Re: Splitting data into two tables based on one field and character in App Development

Splitting data into two tables based on one field and character

heathqm — Mon, 20 Dec 2021 21:05:46 GMT

I want to take one table and split it into two separate tables based on one field and a character inside that field in the dat a load editor.

I currently have a table that looks like this:

Code Description

AAA One code

AAA one position

AAA:B service

AAA:C Agency

AAA:FY Manager

ECT......... with many more rows.

I want to separate this table into two tables one that has all the codes without ':' in them along with their description and then another table that has the codes with ':' in them along with their descriptions. It would look like this:

Table 1:

Code Description

AAA One code

AAA one position

Table 2:

Code Description

AAA:B service

AAA:C Agency

AAA:FY Manager

how would I do this?

Re: Splitting data into two tables based on one field and character

GaryGiles — Thu, 21 Jan 2021 18:23:00 GMT

Try this:

Qualify *;

Table1:

Load Code, Description

Resident current_table

where findoneof(Code,':')=0;

Table2:

Load Code, Description

Resident Current_Table

where findoneof(Code,':')>0;

Unqualify *;

If you rename the columns yourself, you won't need the Qualify and Unqualify statements. This avoids the tables be linked.

Re: Splitting data into two tables based on one field and character

heathqm — Thu, 21 Jan 2021 18:41:42 GMT

That seems to work but the field names are now Table1:Code and Table2:Code. Is there a way to get the table name out of there?

Re: Splitting data into two tables based on one field and character

GaryGiles — Thu, 21 Jan 2021 18:54:20 GMT

Yes, but you don't want the column names to be the same in both tables or Qlik Sense will try to association (link) the tables together in your data model. Why are you trying to separate the tables in the data model?

To rename fields in the load script use Load columname AS newcolumnname

Re: Splitting data into two tables based on one field and character

heathqm — Thu, 21 Jan 2021 18:57:38 GMT

I am okay with the tables being linked I think. I want to separate them because ACC is like a top level code and then everything with a ':' falls under ACC and would be essentially like a bottom level code and I want that to be in a different table for analyzing.

Re: Splitting data into two tables based on one field and character

GaryGiles — Thu, 21 Jan 2021 21:04:26 GMT

It might be cleaner to simply separate the two fields into two distinct columns in the same table. You can then use set analysis in Qlik to separate rows for analysis.

Join (current_table)

Load code,

subfield(code,':',1) as TopLevelCode,

subfield(code,':',1) as SubLevelCode,

if(findoneof(code,':')=0,1,2) as Level

Resident current_table

This would create 3 additional fields in your current table that separate the code value. You still have your original code intact. The Level field will make easy to choose Level 1 or 2. Again, I'm not totally clear on your objective, but this might be a better approach.