Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have data coming from 1 sheet. The data is organised by columns so I have used cross tables to properly display it in table form. I have 5 cross tables now.
I then concatenated these tables to show them in one fact table.
I now have a problem as the data is not shown correctly. Some of these columns should be linked. Is there a better way to link the columns together that should be?
________________________________________________
TableA:
CrossTable(ActionOwner, Owner)
LOAD
[CAR#] As [CAR_CAR#],
[Owner - 1] ,
[Owner - 2] ,
[Owner - 3] ,
[Owner - 4]
FROM
source
TableB:
CrossTable(ActionType,AT1)
LOAD
[CAR#] As [CAR_CAR#],
[Action Type - 1],
[Action Type - 2],
[Action Type - 3],
[Action Type - 4]
FROM
source
TableC:
CrossTable(DateActionClosed,CloseDate)
LOAD
[CAR#] As [CAR_CAR#],
date([Date action closed - 1], 'DD/MM/YYY') as [Date action closed - 1],
date([Date action closed - 2], 'DD/MM/YYY') as [Date action closed - 2],
date([Date action closed - 3], 'DD/MM/YYY') as [Date action closed - 3],
date([Date action closed - 4], 'DD/MM/YYY') as [Date action closed - 4]
FROM
source
TableD:
CrossTable(DateActionInitiated,OpenDate)
LOAD
[CAR#] As [CAR_CAR#],
date([Date action initiated - 1], 'DD/MM/YYY') as [Date action initiated - 1],
date([Date action initiated - 2], 'DD/MM/YYY') as [Date action initiated - 2],
date([Date action initiated - 3], 'DD/MM/YYY') as [Date action initiated - 3],
date([Date action initiated - 4], 'DD/MM/YYY') as [Date action initiated - 4]
FROM
source
TableX:
CrossTable(Action, A1)
LOAD
[CAR#] As [CAR_CAR#],
[Action - 1],
[Action - 2],
[Action - 3],
[Action - 4]
FROM
source
FinalTable:
NoConcatenate
load
*
Resident TableD;
Concatenate
load * Resident TableA;
Concatenate
load * Resident TableB;
Concatenate
load * Resident TableC;
Concatenate
load * Resident TableX;
drop tables TableA, TableB, TableC, TableD, TableX;
_____________________________________________________
This is how the data is currently showing but the rows are not linked.
the data should be linked as ActionOwner1 - Owner1 - OpenDate1 - CloseDate1 - ActionType1
Anyone know how I could do this?
Thanks in advance
Hi,
to join all the columns and have your informations in a single line (for each orw), you should use Join instead of concatenate.
you should know what is the joining key between these tables before proceeding.
Read this:
Thanks for your reply.
If I join these tables, I get this shown in the table. It does not keep the records unique, when they should be.
I should be only seeing one row for each Action.
how do you want to see one row per Action_type if you have different: action_owner, owner, opendate, closedate and age for every action_type ?
maybe I'm missing something here..
What I am looking for as an end result is this. Something which has clear, unique records for each row & column.
So with the use of cross tables, and hard-coding into each expression, I can see what I need to.
I have to supress null values to only show one record of each unique row.
But this now creates a new problem, as one row has to show NULL as the Action can still be open.
Is there a sort of conditional show nulls I can use?