How to handle multiple flags in a single row?

[gorkemakinci]

Hi there everyone,

I'm working on a project where I have multiple flags for my dataset. My dataset has 2 unique ID's and multiple flags on a field. So for example, ID1_1 & ID2_1 data has 5 flags (A|B|C|D|E|F) and ID1_1 & ID2_2 has 4 flags (E|F|G|H) etc.

The problem is my dataset is so big, I tried to seperate my flags in load editor using substringcount with resident load and subfield, it works but since my dataset is so big qlik sense enterprise just gives an error without a code or anything. I also splitted my data before loading to Qlik Sense and it was even worse since I was trying to load for like 30-40 mins and receive an error aftrerwards.

I'm sure there were others who were dealing with hundreds of millions of data, I don't know how to handle this big of a dataset especiallh when it has a field ranging from 1 to 10 distinct flags where I need to filter them seperately. Help me out please, huge thanks in advance!

[Mark_Little]

Hi,

Can you share more details? We have multiple Dashboards with more data than this with no problems. I suspect the issue is either your load script or server power. 

[gorkemakinci]

Problem might be with my data modelling as well I can accept that. Let me try to give you more details. My dataset has around 30 fields and I have another app to create flags for this specific dataset, it filters calculations for every single flag (which are around 150-200 flags in total) and writes that flag in a new field for that specific document number and page number. And I concat those flags cause otherwise it's size significantly increases.

Once I'm done with these steps I've mentioned I have 2 different QVX files, first one being my main dataset with 20-30 fields I've mentioned, and a second QVX which only has 5 fields, being the filename, document number, page number, username id's and the flags. I should also mention all 4 fields except the flags also exists in the original dataset. I have the document and page numbers cause those are distinct (for example a document number has 6 page numbers and only 2 of those page numbers can have a flag but the rest can't, need those 2 field for this distinction.) And I have the filename which is in my fact table, as well as the username id because 2 users can have the same document number since it starts from 1 and just increases one by one.

What I want to achieve is just load this without any problem like data loss or row multiplication, have a filter panel for each and every flag instead of what I have in the screenshot below. Being able to filter it from the panel and being able to see the details on a table below in Qlik Sense sheets.

Here is the screenshot of a sample data from my Flags Table;

The first one is filename, which is in my fact table as I said where I link everything together. Second is document number, third id page number which is like a subset field of document number and the fourth field is just usernames,, and you see my Flags which are just concatenated with | delimiter.

Thanks for all the help again!

[WaltShpuntoff]

@gorkemakinci  I agree with @Mark_Little  here - it would be good to see your code.

One thing you may want to try for your tags is something like this:

TagTable:

Noconcatenate

Load

[YourRowKeyValue],

Subfield([ConsolidatedTagFieldName], '|') as Flag

resident YourTableName;

Notice i did NOT use substring count, or loops of any kind. Just using subfield without the 3rd value will get you all values as separate rows.

[gorkemakinci]

Let me share the load editor code with you, please help me out if I over complicated stuff. 

[FlagCountTable]:
LOAD *,SubStringCount(Flags,'|') as "FlagCount";

LOAD
filename,
"Document Number",
"Page Number",
Username,
Flags
FROM MyFlagTable.qvx (qvx);
Let flagLength = Peek('FlagCount',0,'FlagCountTable');

for i=1 to $(flagLength)+1

[FlagTable]:
LOAD
filename,
"Document Number",
"Page Number",
Username,
SubField(Flags,'|',$(i)) as "Seperated Flags"
RESIDENT [FlagCountTable];
NEXT i;
DROP TABLE [FlagCountTable];

[MainTable]:
LOAD
filename,
"Document Number",
"Page Number",
Username,
.
. (other 25 fields)
.
FROM MyMainQVX.qvx (qvx);

This is how I managed to have all the flags separated and filterable (if that's even a word, but you know what I mean right?), I have other tables as well (which are not creating any keys or anything with these 2 tables, but they do create synthethic keys with themselves and usually if I delete this flag part on this table it takes 5-6 mins to load the whole data. Once I add this flag part it gives an error (not actually an error since there is no error code or anything, I just get disconnected or it says error on loading data and that's it) after 20-25 mins.

Appreciate all the help, hopefully I'll be able to get something out of here and overcome this difficulty in my hands. Thanks a lot again!

[WaltShpuntoff]

Try this instead:

[MainTable]:
LOAD
rowno() as %RowID,
filename,
"Document Number",
"Page Number",
Username,
.
. (other 25 fields)
.
FROM MyMainQVX.qvx (qvx);

[FlagTable]:
NoConcatenate
LOAD
%RowID
SubField(Flags,'|') as "Seperated Flags"
RESIDENT [MainTable];

[gorkemakinci]

Thanks for the guidance but I have quick small questions before I proceed to try it (since it'd end in an error if I don't ask these), I have all the document numbers and page numbers in the main table but I only have a portion of them in the flag table as you can imagine.

Like if there is a flag in document number=4 and page number=16 I have the row on flag table, but if there's not a flag in document number=4 and page number=20, I wouldn't have it on flag table, I only would have that data in main table. When my structure is like this, wouldn't it last as an error to have rowno() on the table?

My second and last question is in your code we doesn't load from the flag table qvx, and I don't know where I should load it for this structure specifically. Thanks a lot for your help again, I'm waiting some more insight on how to try it and I'll leave the results and details here as well.

[WaltShpuntoff]

I used RowNo() as a quick and dirty way of generating a unique row id that could join the two tables. I assumed that the flags field was on that main table.

Qlik likes to have a single key to link tables. You will want to create a compound key if rowno() does not work for you. Make sure to autonumber it at the end to save memory. So - if you need to, create the compound key for each of the tables

It looks like you are trying too hard to control what it does. If you are concerned about null rows for the flags, you can use a where clause WHERE (Len(TRIM(Flags))>0) to filter out rows with no data in them. I use that instead of ISNULL since sometimes you have empty strings, or just spaces due to data entry.

[gorkemakinci]

Is there any way that I can handle what I want to achieve without having Flags field inside the main QVX file? I can create a composite key for sure with the fields I have in common, I'll try it right away.

[gorkemakinci]

Hi there again!

I was able to load without a problem with a composite key, how come can it load so much more data when it's not a synthetic key, I still want to learn more about that. And actually replying to your last pharagraph, I have not a single null row in the flag table, but I have some document numbers on my main table which are not gonna get a flag.

I also do wonder would it be even faster and safer to use ApplyMap for this scenario? And if yes, how can it be used since my flags are not distinct values instead they are concatenated flags like in the screenshot above (ex. 101|202|303|404)? Thanks a lot in advance!