Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

Re: link table convert into one

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)

View solution in original post

9 Replies
Highlighted

Re: link table convert into one

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

Highlighted

Re: link table convert into one

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

Highlighted
Master III
Master III

Re: link table convert into one

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

Highlighted

Re: link table convert into one

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

Highlighted
Master III
Master III

Re: link table convert into one

Hi kush141087‌ , avinashelite‌ / All

Any suggestion?

Thanks

Highlighted

Re: link table convert into one

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

Highlighted
Master III
Master III

Re: link table convert into one

PFA

Highlighted
Master
Master

Re: link table convert into one

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;

Highlighted

Re: link table convert into one

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)

View solution in original post