topic Re: Another how do I? in QlikView

Another how do I?

Sat, 29 Sep 2012 13:01:47 GMT

I am very new to QlikView (a week old) - have done the one hour and 8 hour tutorial but still pretty dumb... I am doing a POC for the business. I appreciate the help.

My fact table has both LastModifiedbyUserCode, CreatedbyUserCode, ClosedByUserCode. They have the same master data table – User Master. How do I make them all point to the User Master.

Re: Another how do I?

Jason_Michaelides — Sun, 30 Sep 2012 11:51:41 GMT

You could use CrossTable() to build yourself a linktable.

I am assuming your Fact table has a row ID of some sort (FactID). LastModifiedUserCode = Lmuc, CreatedByUserCode = Cruc, ClosedByUserCode = Cluc. This also assumes the User Master table has a field called UserCode.

After loading your fact table in the script, put this:

UserLinkTable:

CrossTable(UserAction,UserCode)

Load

FactID,

Lmuc,

Cruc,

Cluc

Resident YourFactTable;

CrossTable() will. Hold a 3-column table with FactID, UserCode and UserAction. Effectively, each row of the fact table will have 3 rows in the link table - one for each of the 3 user code actions (Created, LastModified and Closed).

So now FactID will join your fact table to the link table and UserCode will join the link table to the User Master.

Hope this helps,

Jason

Re: Another how do I?

jagan — Sun, 30 Sep 2012 12:05:05 GMT

Hi,

You can achieve this by joining the master table 3 times with 3 different User code fields.

Table:

LOAD

LastModifiedbyUserCode, CreatedbyUserCode, ClosedByUserCode

FROM TABLE;

Left Join(Table)

LOAD

UserCode AS LastModifiedbyUserCode,

User Name AS LastModifiedUserName

FROM Master;

Left Join(Table)

LOAD

UserCode AS CreatedbyUserCode,

User Name AS CreatedUserName

FROM Master;

Left Join(Table)

LOAD

UserCode AS ClosedByUserCode,

User Name AS ClosedByUserName

FROM Master;

Table:

LOAD

LastModifiedbyUserCode, CreatedbyUserCode, ClosedByUserCode

FROM TABLE;

Modified:

LOAD

UserCode AS LastModifiedbyUserCode,

User Name AS LastModifiedUserName

FROM Master;

Created:

LOAD

UserCode AS CreatedbyUserCode,

User Name AS CreatedUserName

FROM Master;

Closed:

LOAD

UserCode AS ClosedByUserCode,

User Name AS ClosedByUserName

FROM Master;

You can do this by Mapping Load and Applymap. Check help file regarding this.

Thease are the different ways you can achieve this.

Hope this helps you.

Regards,

Jagan.