Prevent lines from merging in table

[TimmyCNHi]

Hi community!

QS is merging some lines for me in a table but I'd like to keep them separate. I'll try to illustrate my intention:

Within the blue area: InitiativeID - project number

Red area: Organization number

Green area: Cost/Saving/Investment

Pink area: Value's for January 2021

While cost (C) is shown separately in the table (green area), the system combined both savings (S). I'd like to retain the different organization numbers (red area), so it would show up like in the source Excel file. How should I approach this?

[rbartley]

It seems to me that there is a problem with the definition of the Orgnization number in your table.  From your example, the organization numbers for the 2 savings records should be distinct and so should be shown as 2 separate lines in the table.  With the information you've provided so far, it's not possible to say why, but I see that there is no Organization number in your table for the 2 S entries.  Why is this? How have you defined it in the table?

[edisonkeith]

Let's say that I have a table with 7 columns with unknown number of rows in all sheets in one workbook excel file. How many sheets? 20.

These tables are located in different cell locations. For example the cell position for first column and first row may be A1 for one table and D7 for another.

[TimmyCNHi]

Hi Rbartley, thank you for your reply. "Org" is defined as such:

=If(IsNull(Organization),' ',Right(Organization,3))

Most values in the table are defined as If(IsNull(VALUE),' ',VALUE) to avoid grey cells with a dash like these:

I've chosen "Right(Organization,3)" as formatting because I only need the last 3 numbers. Stating this value as just itself doesn't change anything:

My question is the same as yours: why are there no separate lines for the 2 S entries?

Here are all the definitions for the fields in the table:

Spoiler

InitiativeID

=If(IsNull(ProjectDescription),' ',ProjectDescription)

SavingsDescription

=If(IsNull(Organization),' ',Right(Organization,3))

=CostOrSaving

=If(IsNull(EuroSaved_A),' ',EuroSaved_A)

=If(IsNull(Date(Start_P)),' ',Date(Start_P))

=If(IsNull(Date(Start_A)),'',If(Year(Start_A)>=PreviousYear and Year(Start_A)<=NextYear,Date(Start_A),''))

If(Year(Start_A)<CurrentYear,'Carry Over','New Project')

=If(IsNull(Start_P-Start_A),' ',Start_P-Start_A)

HSVA

=If(IsNull(IWB),' ',IWB)

=If(IsNull(PillarID),' ',PillarID)

=If(sum([F_2021_01])<=0,' ',sum([F_2021_01]))

(same as above for all 12 months of current year)

=Alt((Sum([F_2021_01])+Sum([F_2021_02])+Sum([F_2021_03])+Sum([F_2021_04])+Sum([F_2021_05])+Sum([F_2021_06])+Sum([F_2021_07])+Sum([F_2021_08])+Sum([F_2021_09])+Sum([F_2021_10])+Sum([F_2021_11])+Sum([F_2021_12])),' ')

I have tried removing all of them, keeping only InitiativeID, Organization and CostOrSaving (in their plain form) but it makes no difference, QS is hell-bent on merging those rows:

[rbartley]

This would be much simpler if you attached your Qlik Sense app.  Is there any way of connecting directly to a database?  Trying to build a Qlik Sense app based on the structure of the Excel file you are describing seems to me to be extremely messy.

[rbartley]

Sorry, I realised that @edisonkeith added a response here, but it is not clear whether this is related to the original post by @TimmyCNHi . My last comment was directed to @TimmyCNHi 

[rbartley]

Could you post your app?  I would have expected 222 and 221 in the organisation column, based on your definition.

[rbartley]

Is this related to the post by @TimmyCNHi ? If not, please create a new post.

[TimmyCNHi]

I've sent the file via private message, I hope it's helpful in any way.

I'm aware this is a very messy practice but our hands are tied. We have a central server for all QS apps and all data sources that are addressed by our apps must reside on that specific server. So databases we have on other servers cannot be accessed directly. Maintaining a copy of a database several gigabytes in size isn't realistic so we've created an Excel on the QS server that imports the data we need from a huge Access database. The Excel is kept up to date on my machine and uploads to the QS server using macros. I know, it's ugly, but it works.

[TimmyCNHi]

Hi @rbartley and everyone. Any update on this?