topic Re: link table convert into one in QlikView

link table convert into one

prma7799 — Mon, 23 May 2016 06:46:14 GMT

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

Re: link table convert into one

Kushal_Chawda — Mon, 23 May 2016 06:54:40 GMT

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

Re: link table convert into one

avinashelite — Mon, 23 May 2016 06:57:30 GMT

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

Re: link table convert into one

prma7799 — Mon, 23 May 2016 06:57:51 GMT

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

Re: link table convert into one

Kushal_Chawda — Mon, 23 May 2016 07:04:55 GMT

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

Re: link table convert into one

prma7799 — Mon, 23 May 2016 07:42:40 GMT

Hi kush141087‌ , avinashelite‌ / All

Any suggestion?

Thanks

Re: link table convert into one

Kushal_Chawda — Mon, 23 May 2016 07:44:45 GMT

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

Re: link table convert into one

prma7799 — Mon, 23 May 2016 08:10:36 GMT

PFA

Re: link table convert into one

effinty2112 — Mon, 23 May 2016 08:41:36 GMT

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;

Re: link table convert into one

MayilVahanan — Mon, 23 May 2016 08:49:00 GMT

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)