Split in Qlik Sense

[Gsab1]

I am seeing strange behaviour trying to split columns from imported CSV files particularly trying to split on comma separated words.

I noticed there is a bug noted here:

https://community.qlik.com/t5/New-to-Qlik-Sense/Data-Manager-Split-Card-These-Positions-button-greye...

Should I persevere trying to understand why often the split 'all instances' button is greyed out or is this a known issue.

Thanks

Glen 

[ArnadoSandoval]

Hi GSab1

I replied to your post few second ago, a long reply but this interface decided to reject my reply, with a message like the "ticket expired" or something like that (no your fault), so, here I go again, now, please understand it is not easy to re-write exactly the same, so, I will try my best this time around. I will be writing-posting paragraph by paragraph, in my attempt to beat this interface.

I played around with the data used on the post you referenced trying to replicate the bug, I was unable, the bug was initially reported on Feb-2019, and reported as fixed in May-2019, I am working with the November-2019 version and did not find any offending behaviour, I did some test before reaching my conclusion. These are the tests I did:

1. The initial selection given at the referenced post; basically selecting the whole records, and observing that the All Instances and These Positions buttons are disabled, this is expected behaviour.

Once you click on the This Instance button you get the result shown below, two columns but the record "C0128Z00005296GL871" is nowhere to be seen, later in this reply you will find the reason for that to happen.

2. I selected the first five characters of the record, e.g. "C0128", once again, only the This Instance button is available; I will take this as standard behaviour.

Once I clicked on the This Instance button I got the record split in two columns; there were 3 records in the sample data starting with "C0128", these records were imported without the text "C0128", it is like the Split Card took it as a field separator!

3. This time, after selecting the letter Z, only the This Instance and These Positions buttons become available, so we tested both of them. 

Once we clicked the This Instance button we got a result table with two columns, while the character Z was excluded from the result, just like the Split Card took it as a field separator.

While the resulting table after pressing on the These Positions returned 3 columns, with the letter Z as the second column, this result got nothing to do with the letter Z itself, it just happens that the sample data has the letter Z at the same position in all the records!

4. This time we just highlighted the number two on the sample record; this character, number two appears twice on the record, as a result all three buttons are available,  the Split Card named instance when the selected character appears multiple time on the sample record !!!

When we clicked on the All Instance button, the resulting table features three columns, split at the character 2, e.g. this character become a field separator!!! You should observe that the character '2' was excluded from the result, just a field separators do. You should also observe those records having just one single instance of the character 2 do not have three columns, their third column is null.

When we clicked on the These Positions button, the Split Card split the records in three columns as well,  but this time using the start-end position of the selected character! 

Conclusions:

1. I did not find any bug performing my tests, the Split Card behaved as expected.
2. It could be possible, your data is different to the one used, if such is the case, would you please share it in this thread elaborating more about the issue you are having. I could have missed something.
3. I am confident to write that the "All Instances" button is enabled when the character you select on the sample record appears more than once, e.g. multiple-times, e.g. multiple instances; otherwise it remains disabled.

I managed to retype my reply, it is close enough to the first one I wrote, the essence is there.

Hope this helps,

Arnaldo Sandoval

[Gsab1]

Hi,

Thank you so much for detailing your test. Amazed and happy that you helped with so much explanation AND had the resolve to redo it all !

I'm hoping it's just me not understanding things properly. I did massilvely simplify my testing to try and better understand what the 3 buttons do in the split card.  While I do more tests I did think it worth mentioning that I mostly got the behaviour I was expecting and that you showed in your expamples. For some reasons it was the comma seperators that were causing trouble.

Thanks again

Glen

[Gsab1]

Before I add more.....I also just lost what I was writing....Not ideal...anyway.

Im also having trouble with the Split card not showing the reimported data, not sure if that has to do with it.

I removed a space after a comma and it looks correct here

But in the split view the space is still there:

 

 

[Gsab1]

And finally here is an example of the 'split all instances' not working as I thought it should:

'all instance' button greyed out

'all instance' button active 

At one point I thought it was to do with making sure to select the field that had the longest string but from what I remember that didn't work.

[ArnadoSandoval]

Hi Gsab1

When you select the comma on the Jim, jones text, there is a single comma or a single instance of a comma in the text, that is why All Instances button is disabled.

When you select a comma on the text: Simon, Jill,Peter there are two commas or two instances of a comma in the text, therefore the All Instances button is enabled.

The All Instances button react to your text selection, no the whole data from the file.

Hope this helps,

Arnaldo Sandoval

[Gsab1]

Hi Anoldo,

It makes a lot more sense now. It's still a bit confusing why if I do get the 'all instances' button highlighted I am able to separate all the rows of data not just the one selected. It seems strange that I cannot separate the column of data in one go.  Guessing its my misunderstanding. Thanks again for the help on this.

[ArnadoSandoval]

Hi @Gsab1 

You should select the most representative row of data of the whole table, the selected record define the splitting template to be applied to the whole table-column.

Hope this helps,

Arnaldo Sandoval

[Gsab1]

Hi again,

That's kind of where I got to before asking for help here. Understanding the 'rule' that allows me to apply the split to all instances (of a comma) bamboozled me..I thought it was picking the longest set of data in a row. When I projected what this means for importing data that is hundreds of rows long on multiple columns of data it all sounded quite daunting. 

I'll perhaps look into learning how to do similar using a script on import or somethings. Thanks again for you help. 

G

[ArnadoSandoval]

Hi @Gsab1 

Actually the Data Manager feature in Qlik Sense is very powerful, but it could be very confusing as well, in my opinion, it is there for users with limited knowledge of scripting, or to implement quick dashboards when the data is pretty clean; but for those with programming skills, mastering the Qlik's Scripting Language,  you feel you got more power; actually in my opinion as well, the Scripting Language is mighty powerful.

Best Regards,

Arnaldo Sandoval