Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have one application in that I have 5 fact table using one common key I have create link table.
Now I want to remove that link table and want all fact into one table (like concatenate) because it is take too much time to reload.
So anyone please give some suggestion on this.
Thanks
Hi
Concatenate all the fact table and create one fact table.. And create flag field to identify the table name.. For Ex:
Fact:
Load *, 'Fact1' as Flag From Fact1;
Concatenate(Fact)
Load *, 'Fact2' as Flag From Fact2;
Concatenate(Fact)
Load *, 'Fact3' as Flag From Fact3;
And use expression like this
=Sum({<Flag = {'Fact1'}>} YourMeasureFied)
Then use concatenate, rename all the fields in fact table with matching data type and create the Flags
Fact1:
LOAD KeyFact1 as Key,
MeasureFact1 as Measure,
'Fact1' as Flag
From Fact1;
concatenate(Fact1)
LOAD KeyFact2 as Key,
MeasureFact2 as Measure,
'Fact2' as Flag
From Fact2;
.....
....
Like for all 5 facts
share the sample data and script so that will be helpful for us to suggest the solution
Hi Kush,
Thanks for reply...
This is for Match field
Fact1:
LOAD KeyFact1 as Key,
MeasureFact1 as Measure,
'Fact1' as Flag
From Fact1;
concatenate(Fact1)
LOAD KeyFact2 as Key,
MeasureFact2 as Measure,
'Fact2' as Flag
From Fact2;
.....
....
Like for all 5 facts
but what about unmatched field which is present in all fact tables.
Thanks
share sample data for all 5 fact tables with all field name.
I don't have qlikview in my system. Can you send screenshot or excel file?
PFA
Try this code to join your tables up into a big fact table:
Set vTabCount = NoOfTables();
For i = $(vTabCount) -1 to 0 step -1
If TableName($(i)) <> 'LinkTable' Then
Let vTableName = TableName($(i));
Left Join (LinkTable)
LOAD * Resident $(vTableName) ;
DROP Table $(vTableName) ;
End if;
NEXT i;
RENAME Table LinkTable to Facts;
Hi
Concatenate all the fact table and create one fact table.. And create flag field to identify the table name.. For Ex:
Fact:
Load *, 'Fact1' as Flag From Fact1;
Concatenate(Fact)
Load *, 'Fact2' as Flag From Fact2;
Concatenate(Fact)
Load *, 'Fact3' as Flag From Fact3;
And use expression like this
=Sum({<Flag = {'Fact1'}>} YourMeasureFied)