Data upload error excel

[Kirsten]

I am currently working with uploads of excel through sharepoint. 

Initial upload did run smoothly but with the second upload something seems to go wrong with the merge of the datafiles, which is creating duplicate months in the graph. I thought this was due to cells with different formatting in excel where, though I corrected formatting in excel and the issue still occurs 

How can I avoid having duplicate month values in my graph.

See attachment with the example on page 2

[Michael_Tarallo]

Hi Kirsten - I may need a bit more information - but it may be because we automatically convert date fields into their related date dimensions. So for example if your data has Month (10) and Date (10/02/2020) - we will take Date and break it down into new fields. I'm curious if that could be the issue you are running into. If you can attach some screen captures of your field lists that would be helpful. I am assuming you are using the graphical data manager to define the data model?

[Kirsten]

Hi @Michael_Tarallo 

I have attached a file with the first 2 lines from each datafile. 

I upload my files on sharepoint, work with sharepoint connector. Then in QlikSense: data load editor/ Create new connection/Webfile   , add link from the  sharepoint connector tool.  Insert script from the connection on the left/load data and then in datamanager I connect the datapoints 

[Michael_Tarallo]

OK so what I am seeing is that all files have the same field structure (except Material PPG) so technically Files 1 - 3 should auto concatenate - and create 1 table. If any of the field structure is different (extra fields, missing fields) it will attempt to create it as a separate table and then link the two tables together on a similar field such as YearMonth. Is it possible the data layout in File 3 is slightly off? - can you share the data mode viewer screenshot of your model:

[Kirsten]

@Michael_Tarallo 

See attachment. I added the datamodel (from PPG I removed Material Desc and PPG. in the example file but is in my datamodel). I also added a screenshots with the steps for upload. And the third image in the attachment shows the cell formatting for the date field (custom ####-##) I applied this formatting to all date cells in excel

[Michael_Tarallo]

Hi Kirsten - I am going to attempt to reproduce this - but I noticed that you have 2 Service Area fields:

Service Area

and

Service Area.  <--- with a "."

Is that intentional? 

Your layout  that you sent me only shows (1) Service area field

let me know

Thanks

[Kirsten]

@Michael_Tarallo 

Yes the additional service area field is intentional but I deleted this in the example file. I did get a bit further in my investigation. If you look at the linegraph in my first message one date is formatted like this 2019-01 and the months at  the end like this 201901. Althoug in my excels there is no cell that contains the date in this format 201901 (see attachment, you can see this in the filter). Think something goes wrong somewhere in the processing in QlikSense? Although I want to do one exercise. First I changed all  the date cells with "format painter"  in excel to make a similar format in all the cells, now I want to literally copy one date cell to the other cells. So one cell 2019-01 to the other cells from 2019-01. I can do this tomorrow, shall I let you know the outcome tomorrow? If it still doesn't work then do the  investigation?  I can then send you the complete data files to your work email, can not post it on the forum due to confidentiality 

[Michael_Tarallo]

Cool - yes please and I will send you a PM with my email. 

[Kirsten · Accepted solution]

Thanks issue resolved. Attached the solution I received from Mike