Splitting data into two tables based on one field and character

[heathqm]

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?

[GaryGiles]

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.

[heathqm]

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? 

[GaryGiles]

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

[heathqm]

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. 

[GaryGiles · Accepted solution]

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.