Help with lining up in a Data Table

[#1]

Hi,

I was wondering if anyone could help with a problem I am having. I am unsure of the best way to load the Data into Qlikview.

What i want is something like up the top where for each ClientID i can have a data table and for each Client ID line up the rates on the same line so i can compare moth on month.

The problem is the data is coming from different files and the some clients may not be on one of the rates. So i can't manually in Excel line them up side by side and load them into Qlikview.

Down below is the way i am trying to load in the data but i am getting loads of duplicates. This is only mock data at the moment so when i load it in i join ClientID's and ClientID to match up the Clients Name.

Can anybody help me with a way to fix this, I am in a rush so if doesn't make much sense let me know and i will try explain it better.

Attached is the Excel file of how i am trying to load in the Data.

Thanks,

[Anonymous]

Hi foldingftw,

As far as I understand you need to generate a table like the one at the top of your spreadsheet, if that so, you can achieve it by left joining all your data sources. I'm attaching an spreadsheet with data (I hope I understand well the structure) and a sample qvw.

Regards,

-- Karla

[#3]

Hi Karla,

Thank you for the reply, unfortunately i can't open your file as i only have the personal edition.

Do you mean a left join like in the attached photo.

If i  load in the data the way i had it in my Excel file.

[Anonymous]

Hi foldingftw,

Your code generates a table like the one you show from row 13 in the spreadsheet that you shared in your first post, doesn't it?. In that case you could group by your data. After your left join block (checking your screenshot it should be after line 33) try to adding something like:

NoConcatenate

LOAD

  CLIENTID,

  Merchant_Names,

  Sum([Feb FX Rate]) as [Feb FX Rate],

  sum([Mar FX Rate]) as [Mar FX Rate],

  sum([Feb RX Rate]) as [Feb RX Rate],

  sum([Mar RX Rate]) as [Mar RX Rate]

Resident Data

Group By CLIENTID, Merchant_Names;

Drop table source;

Regards,

--Karla

[#5]

Got it sorted Karla,

Thanks.

[qliksus · Accepted solution]

You can also use crossable for your case as QV stores data only once