Looking for Ideas

[Markbhai]

Hi All,

I have some fields, where data is captured from 'Option Boxes' in the parent application and these are collected in the database as a string of options separated by commas, eg. the 'type of personal data' field might contain: name, address, ip_address, health_data ... (you get the picture).  There can be more that one item selected (hence the comma separation)

Assuming it is the right approach, I need to understand the best way to import this into Qlik to encourage good analysis.

I should also say that I am such a newbie, that currently I have only used Data Manager and have Zero experience with the Load Editor yet.

Thanks in advance.

Mark.

[F_B]

Hi,

Qlik Sense recognizes different fields if separated by commas in text files:

Loading data from files | Qlik Sense on Windows Help

Maybe, instead of loading the 'type of personal data' field as a big container, you could go down a level and load the name, address, ip_address, health_data fields...

Bye

[steeefan]

You can break up such fields using the SubField() function:

Data:
LOAD
  Id,
  Name,
  SubField(TypeOfPersonalData, ',') AS PersonalInfo
FROM
  [lib://yourFile.qvd]
  (qvd);

If you have one user in the QVD file that has five entries in TypeOfPersonalData, the script above will create a table with five rows, all containing the same value in ID and five different values in TypeOfPersonalData.

You should therefore load the data from QVD first in full without any transformations, then get a separate table with the unique identifier of the record, here ID, and the SubField() function. This will however not tell you which information you are getting. So if the position of theinformation varies between users, you'd have to figure that out in a second step.

[Markbhai]

Thanks Steefan,

The data is currently held in a SQL database alongside other fields, such as 'Activity Name', 'Activity Description' 'Personal Data (Y/N)' 'TypeofPersonalData'

Do I need to create a new db Table for TypeofPersonalData?

As mentioned previously, I havent used Load editor yet, so very much in the early stages of learning.

[steeefan · Accepted solution]

No, you just have to get the data from that table into Qlik Sense. This could look sth. like this:

LIB CONNECT TO 'SQL Server';

Data:
LOAD
  UserId,
  ActivityName,
  ActivityDescription,
  TypeOfPersonalData;
SELECT
  UserId,
  ActivityName,
  ActivityDescription,
  TypeOfPersonalData
FROM
  "DB".dbo."tablename";


PersonalData:
NOCONCOCATENATE LOAD
  UserId,
  SubField(TypeOfPersonalData, ',') AS PersonalInfo
RESIDENT
  Data;

This script will create an active connection to your SQL server, given you have created an SQL data connection with that name and load data from table tablename into Qlik Sense into the table Data.

After that, data will be loaded from the internal table Data and the field TypeOfPersonalData will be exploded at each comma so that you will have rows for each single entry, as stated before:

"If you have one user in the QVD file that has five entries in TypeOfPersonalData, the script above will create a table with five rows, all containing the same value in ID and five different values in TypeOfPersonalData."

[Markbhai]

Thanks, I will give this a go and see what I can learn.

I also found this in Data Manager :

It seems to do a similar job (maybe even the same), but because of the structure of the CSV values it doesnt put the same derived variables in the same field so I will no doubt need to solve that issue next. 🙂

Thank you.

[Arch00]

Hello, everyone. I'm newbie.Glad to be here. Have a good day, everybody.🙂

[Markbhai]

Hopefully one last question to close this off. 

I have got the table working as I would like now (thanks), but I notice that I now have two tables one of which is not used.

Do I need to drop the unused table?

Here is the load script I used:

LIB CONNECT TO 'dbname_prod_rds_dbname;

nondisclosure:
Load
id,
informtion_non_disclosure_options;
Select
id,
informtion_non_disclosure_options
FROM `dbname`.foi;

nondisclosure:
NoConcatenate Load
id,
SubField(informtion_non_disclosure_options, ',') As [NonDisclosureReason]
RESIDENT
nondisclosure;

Cheers

Mark

[steeefan]

You are creating the table nondisclosure when loading from the DB:

nondisclosure:
Load
  id,
  informtion_non_disclosure_options;
Select
  id,
  informtion_non_disclosure_options
FROM
  `dbname`.foi;

Right after that, you are using that table name again when creating the next table:

nondisclosure:
NoConcatenate Load
  id,
  SubField(informtion_non_disclosure_options, ',') As [NonDisclosureReason]
RESIDENT
  nondisclosure;

This then gives you two tables, nondisclosure and nondisclosure-2. That is not good practice and needs to be avoided at all times. For cases such as this, use a temp. table name for your first table, such as nondisclosure_stg1, nondisclosure_tmp1 etc. Perform your RESIDENT LOAD based on that, giving this second table the name you want to have it in the end, i.e. nondisclosure, and then DROP TABLE nondisclosure_stg1.

[Markbhai]

Thanks once again for your advice.

Mark