Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How do I prevent data fields from being combined?

Hi,

I have this data set where I have several entries containing the date in the DD/MM/YYYY format. Since I don't want it to be displayed that way, I used

MonthName("Date") as Date

in the data load editor to load the data. There are supposed to be 11 data points in that data set.

However, when I do that I lose 1 data point! There are two rows in my excel file that are exactly the same bar one field, and those 2 got combined. I do not want that to happen, so what do I have to do?

I know that they got combined in some way because when I applied the Count(Date) to a certain visualisation that I had, it gave me a value of 10 instead of 11.

1 Solution

Accepted Solutions
Employee
Employee

Re: How do I prevent data fields from being combined?

The above tables have multiple field that are identical, that generates a synthetic table. My recommendation is that you eliminate synthetic tables, as this will give you a more predictable data model. There are many ways to approach this problem, and it all depends on what you are trying to do and how your data relates.

The key thing is to only have one common field in the linked tables. Simply this means you tables need to be renamed in some way.

12 Replies
ramoncova06
Valued Contributor III

Re: How do I prevent data fields from being combined?

would have to look at the data set, could be that you had two different days in the same month ?

Not applicable

Re: How do I prevent data fields from being combined?

It is work related data, so I am afraid that I cannot share the data here, but yes they were 2 different days. The entire data set that I had contains data from a particular month and it just happens that those 2 data points got combined.

To provide more context, the dataset is some sort an error report and the two points that got combined were errors regarding the same system, and thus they are almost identical in terms of content apart from the day and the report ID. If more information is really needed, I would try to see how I "clean" the data to hide the sensitive information

ramoncova06
Valued Contributor III

Re: How do I prevent data fields from being combined?

no worries about the data, is just that it would provide a more cleaner picture

since your main issue is with the date format do not use monthname, instead use date(fielddate,'MMM YYYY') that way it will not group all of the different dates into a same value

Employee
Employee

Re: How do I prevent data fields from being combined?

Please provide a sample of the script so that it is possible to see how you combine the data during load.

Not applicable

Re: How do I prevent data fields from being combined?

Hi Ramon,

That does solve the problem of the data fields combining, but I want to collate all data points in February and present them in a monthly view, if I do it in that manner, they still show up as separate days when thrown into my visualisation. That is, I want it to show as a stacked bar of all the errors in the month of February and for the value of the stacked bar to be 11 instead of 10.

Thanks,

Yun Zheng

Not applicable

Re: How do I prevent data fields from being combined?

Hi Toni,

Initially I was using this

LOAD

MonthName("Date") as “Date”,

S.No,

System,

"Job Name",

"Start Time",

"End Time",

Status,

"Mode",

"Problem

Reported

date",

"Resolved

date",

"Job Number",

" Job

owner",

"Status / Remarks",

FROM

ramoncova06
Valued Contributor III

Re: How do I prevent data fields from being combined?

you keep throwing curve balls at me , how is you count or sum being done ?  based on the date ?

try creating one field with the month name and another with the date format, and use the monthname one as the dimension in you chart

another option is to create a calculated dimension in the chart  I have used something like this before date(MonthStart(datefield),'MMM YYYY')

Not applicable

Re: How do I prevent data fields from being combined?

Hi Ramon,

My count is done based on the Name of the system, so I have a stacked bar chart, with each colour representing a different system. Therefore my 2 dimensions are date and system, while my dimension is count(system).

I will give what you have suggested a try later and provide an update then.

Regards,

Yun Zheng

Employee
Employee

Re: How do I prevent data fields from being combined?

The sample above can impossible explain that two rows get merged. There are many ways in which you might "accidentally" merge data. Unfortunately, I am not able to take time and list them all to you, so if you could provide a complete sample script it would make it easier to find the root cause.

Please provide the complete script including any joins or concatenations you do that involves this table.

Community Browser