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!

[WaltShpuntoff]

Synthetic keys are expensive in terms of system resources and need to be avoided. They consume additional memory - and  "guess" the data relationships - creating keys where needed that may or may not be correct.

ApplyMap does not work for your situtation. Think of it as key-value pairs where the applymap does a translation. It is a great function, but it is 1 key -> 1 value only.

"I have some document numbers on my main table which are not gonna get a flag." - that is what I would expect. If you need to be able to select those records, you may need to create a '-No Tags-' record and append it to your tags table where it is appropriate.

Sounds like you have things under control

-ws

[marcus_sommer]

I'm not sure if your approach is really expedient for Qlik else I have the impression it's too complicated by transferring an external logic - probably sql-based - into Qlik.

I think I would simply join/map the flags to the facts. In general the single flag-field would already fulfill the requirements to select the data-set to the wanted sub-set. I assume it would be only in this way not user-friendly enough.

But the next step might be to create from it a dimension-table by removing the id-related flag-parts of filename, page and user and just keeping the others - as single field and/or also divided in separate fields.

Therefore I suggest to re-thing the entire approach.

[gorkemakinci]

Most probably you're right on data modelling I've done on Qlik Sense in general for sure, but for this specific issue I had, I was already using an external software to do various calculations for assigning the flags. The other software was having a hard time giving me the with splitted one key for row logic. That's actually why I had to try and handle this issue from Qlik.

Just to basically sum it up, I have documents which I have to do calculations and (single or multiple) assign keys to them (with detail of document number as well as page number), would Qlik to any better doing these calculations if yes how exactly I don't know. Therefore I do them in another software but I kinda had to receive the output with concatenated flags for each unique document number&page number.

For all these steps I've done above, what would you do or use instead to make it happen?

[marcus_sommer]

It sounds that there are multiple pre-calculations whereby rather not as aggregations else to create some kind of grouping. For both types it might be useful to do such measures but I would go with such an approach only if the UI is really not performant enough - means such pre-calculations wouldn't be the first step in the development else rather the last one.

The handling of multiple pre-calculations respectively states within the script could become quite difficult - if they are in different tables. Therefore I would try to merge all into a single table just by concatenating them which means a logic like the following should work within the most scenarios: Fact Table with Mixed Granularity - Qlik Community - 1468238.