Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Mary,
Could you provide an example data set or at least Table and Column descriptions?
Thanks
Oscar
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.
Why don't you just build a link table containing the following fields:
AccountID
AccountName
Date
?
Mary,
Based on the data provided what are your expected results?
Thanks
Oscar
I guess that:
Mary,
Maybe something like this?
Good Luck
Oscar
Thanks Oscar, I will give it a go and let you know how it goes
Thank you so much for your help Oscar
It is working
Have a great weekend.