Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
ToniKautto
Employee
Employee

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.

View solution in original post

12 Replies
ramoncova06
Specialist III
Specialist III

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

Not applicable
Author

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
Specialist III
Specialist III

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

ToniKautto
Employee
Employee

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

Not applicable
Author

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
Author

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
Specialist III
Specialist III

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
Author

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

ToniKautto
Employee
Employee

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.