Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prma7799
Master III
Master III

link table convert into one

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 

1 Solution

Accepted Solutions
MayilVahanan

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)

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

9 Replies
Kushal_Chawda

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

avinashelite

share the sample data and script so that will be helpful for us to suggest the solution

prma7799
Master III
Master III
Author

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

Kushal_Chawda

share sample data for all 5 fact tables with all field name.

prma7799
Master III
Master III
Author

Hi kush141087‌ , avinashelite‌ / All

Any suggestion?

Thanks

Kushal_Chawda

I don't have qlikview in my system. Can you send screenshot or excel file?

prma7799
Master III
Master III
Author

PFA

effinty2112
Master
Master

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;

MayilVahanan

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)

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.