Lookup multiple tables against static table and matching in pivot table

[#1]

Hi all,

Please can you assist.

I have a dashboard reading from 5 different text files, joining by the date of the report.

I have another table that only contains the Account ID and Account Names (Accounts table). The 5 text files must lookup to the Account ID and return the relevant Account Names.

The aim is to create a pivot table that shows all the Account name from the Accounts table a file (as a column) and the expressions to be a lookup to the 5 text files, if it can find the Account ID return how many times it appears.

I have tried numerous times, however had no luck. 

For example, joining the 5 text files to Accounts table in the script.

Then creating a pivot table with the Account Name from the individual file and a Set Analysis formula that identifies.

Thank you so much for reading.

[oscar_ortiz]

Mary,

Could you provide an example data set or at least Table and Column descriptions?

Thanks

Oscar

[#3]

Thank you for the prompt reply Oscar.

I have attached both a QlikView and  Excel example.

QlikView:

I have: 3 tables: PG, NR and UN these are all joined by the date.

1 Static table containing the Account ID and Account Name.

I would like to join the PG/NR and UN to the Account ID (in the Account table) to return the Account name and have it all in one table.

PG joined to Account ID by the PG_AccountID column in the PG table

NR joined by NR_Account in the NR table

UR joined by UN_Account ID in the UR table.

I have updated my expresions to reflect this e.g. "=count({$<[Account ID]=P([UN_Account ID])>}[UN_Account ID]) " But it is not working.

Excel

PG/NR/UR tabs are the raw data. Static table shows the Account ID and Account Name.

Summary tab is what we want to show.

I hope that make sense...

Thanks again.

[Anonymous]

Why don't you just build a link table containing the following fields:

AccountID

AccountName

Date

?

[oscar_ortiz]

Mary,

Based on the data provided what are your expected results?

Thanks

Oscar

[Anonymous]

I guess that:

[oscar_ortiz · Accepted solution]

Mary,

Maybe something like this?

Good Luck

Oscar

[#8]

Thanks Oscar, I will give it a go and let you know how it goes

[#9]

Thank you so much for your help Oscar

It is working

Have a great weekend.