multiple fields the same removal of synkey

[Tigger]

Hi 

I am relatively new to qliksense, I have several fields in 3 data tables ( 2016, 2017, 2018) that are the same. I am obviously receiving a synkey- how would I remove the synkey ( I cannot change all the field names). I have auto concatenated the three years into one table.  pls see script below and data model attached.

Data2016:
LOAD
// Category,
"State/UT",
"Cases Reported",
"Child Victims of Rape (Below 18 Yrs) - Below 6 Years",
"Child Victims of Rape (Below 18 Yrs) - 6 Years & Above - Below 12 Years",
"Child Victims of Rape (Below 18 Yrs) - 12 Years & Above - Below 16 Years",
"Child Victims of Rape (Below 18 Yrs) - 16 Years & Above - Below 18 Years",
"Child Victims of Rape (Below 18 Yrs) - Total Girl /Child Victims",
"Women Victims of Rape (Above 18 Yrs) - 18 Years & Above - Below 30 Years",
"Women Victims of Rape (Above 18 Yrs) - 30 Years & Above - Below 45 Years",
"Women Victims of Rape (Above 18 Yrs) - 45 Years & Above - Below 60 Years",
"Women Victims of Rape (Above 18 Yrs) - 60 Years & Above",
"Women Victims of Rape (Above 18 Yrs) - Total Women/Adult Victims",
"Total Victims"
FROM [lib://Google_Drive - terrencewepener@gmail.com/1m7wqDP83tU4KoyBhYuGOybbwgvnl7F7W/2016 Victims of Rape.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Rape2016:
load*,
2016 as Year

Resident Data2016;

Drop Table Data2016;

Data2017:
LOAD
// Category,
"State/UT",
"Cases Reported",
"Child Victims of Rape (Below 18 Yrs) - Below 6 Years",
"Child Victims of Rape (Below 18 Yrs) - 6 Years & Above - Below 12 Years",
"Child Victims of Rape (Below 18 Yrs) - 12 Years & Above - Below 16 Years",
"Child Victims of Rape (Below 18 Yrs) - 16 Years & Above - Below 18 Years",
"Child Victims of Rape (Below 18 Yrs) - Total Girl /Child Victims",
"Women Victims of Rape (Above 18 Yrs) - 18 Years & Above - Below 30 Years",
"Women Victims of Rape (Above 18 Yrs) - 30 Years & Above - Below 45 Years",
"Women Victims of Rape (Above 18 Yrs) - 45 Years & Above - Below 60 Years",
"Women Victims of Rape (Above 18 Yrs) - 60 Years & Above",
"Women Victims of Rape (Above 18 Yrs) - Total Women/Adult Victims",
"Total Victims"
FROM [lib://Google_Drive - terrencewepener@gmail.com/1m7wqDP83tU4KoyBhYuGOybbwgvnl7F7W/2017 Victims of Rape.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Rape2017:
load*,
2017 as Year

Resident Data2017;

Drop Table Data2017;

Data2018:
LOAD
// Category,
"State/UT ",
"Cases Reported",
"Child Victims of Rape (Below 18 Yrs) - Below 6 Years",
"Child Victims of Rape (Below 18 Yrs) - 6 Years & Above ",
"Child Victims of Rape (Below 18 Yrs) - 12 Years & Above ",
"Child Victims of Rape (Below 18 Yrs) - 16 Years & Above ",
"Child Victims of Rape (Below 18 Yrs) - Total Girl/Child Victims",
"Women Victims of Rape (Above 18 Yrs) - 18 Years & Above - Below 30 Years",
"Women Victims of Rape (Above 18 Yrs) - 30 Years & Above - Below 45 Years",
"Women Victims of Rape (Above 18 Yrs) - 45 Years & Above - Below 60 Years",
"Women Victims of Rape (Above 18 Yrs) - 60 Years & Above",
"Women Victims of Rape (Above 18 Yrs) - Total Women/Adult Victims",
"Total Victims"
FROM [lib://Google_Drive - terrencewepener@gmail.com/1m7wqDP83tU4KoyBhYuGOybbwgvnl7F7W/2018 Victims of Rape.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Rape2018:
load*,
2018 as Year

Resident Data2018;

Drop Table Data2018;

[JordyWegman]

HI Tigger,

Try this code while it's easier to maintain:

RapeData:
Load * Inline[
Year
];

For i = 2016 to 2018 step 1

  Concatenate( RapeData )
  LOAD
    $(i) as Year,
    // Category,
    "State/UT",
    "Cases Reported",
    "Child Victims of Rape (Below 18 Yrs) - Below 6 Years",
    "Child Victims of Rape (Below 18 Yrs) - 6 Years & Above - Below 12 Years",
    "Child Victims of Rape (Below 18 Yrs) - 12 Years & Above - Below 16 Years",
    "Child Victims of Rape (Below 18 Yrs) - 16 Years & Above - Below 18 Years",
    "Child Victims of Rape (Below 18 Yrs) - Total Girl /Child Victims",
    "Women Victims of Rape (Above 18 Yrs) - 18 Years & Above - Below 30 Years",
    "Women Victims of Rape (Above 18 Yrs) - 30 Years & Above - Below 45 Years",
    "Women Victims of Rape (Above 18 Yrs) - 45 Years & Above - Below 60 Years",
    "Women Victims of Rape (Above 18 Yrs) - 60 Years & Above",
    "Women Victims of Rape (Above 18 Yrs) - Total Women/Adult Victims",
    "Total Victims"
  FROM [lib://Google_Drive - terrencewepener@gmail.com/1m7wqDP83tU4KoyBhYuGOybbwgvnl7F7W/$(i) Victims of Rape.csv]
  (txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Next i

Both in the Year field and the filename I've added $(i) so the year will be filled in here.

Jordy

Climber

[JordyWegman]

Hi Tigger,

You would have to replace your whole code by my code.

Jordy

[Tigger]

Hi Jordy,

Thanks for the message. Can you show an example of more than one table in your explanation/solution pls. 

I am still receiving a synkey when I load more than 2 tables with multiple field names being the same. I am now a little confused as to whether I should keep the tables separated with no concat or attempt to concatenate to remove the synkeys .. or create a key to associate the tables.. (this is difficult because there are multiple field names the same). 

Ultimately I would want all on 1 table separated by the year.  Thanks for the assistance. 

[Tigger]

Hi Jordy, 

Thank you.  i= 2016-2018,  I am only receiving 2016 data.  How would I load 2017 and 2018 ? 

[JordyWegman]

Hi Tigger,

The For .. Next loop should do that for you. Do you have some example data?

Jordy

Climber