topic Re: How to link rows in same table in QlikView

How to link rows in same table

Qlik_ULG — Wed, 21 Feb 2018 16:24:56 GMT

Hi everyone,

I have data coming from 1 sheet. The data is organised by columns so I have used cross tables to properly display it in table form. I have 5 cross tables now.

I then concatenated these tables to show them in one fact table.

I now have a problem as the data is not shown correctly. Some of these columns should be linked. Is there a better way to link the columns together that should be?

________________________________________________

TableA:

CrossTable(ActionOwner, Owner)

LOAD

[CAR#] As [CAR_CAR#],

[Owner - 1] ,

[Owner - 2] ,

[Owner - 3] ,

[Owner - 4]

FROM

source

TableB:

CrossTable(ActionType,AT1)

LOAD

[CAR#] As [CAR_CAR#],

[Action Type - 1],

[Action Type - 2],

[Action Type - 3],

[Action Type - 4]

FROM

source

TableC:

CrossTable(DateActionClosed,CloseDate)

LOAD

[CAR#] As [CAR_CAR#],

date([Date action closed - 1], 'DD/MM/YYY') as [Date action closed - 1],

date([Date action closed - 2], 'DD/MM/YYY') as [Date action closed - 2],

date([Date action closed - 3], 'DD/MM/YYY') as [Date action closed - 3],

date([Date action closed - 4], 'DD/MM/YYY') as [Date action closed - 4]

FROM

source

TableD:

CrossTable(DateActionInitiated,OpenDate)

LOAD

[CAR#] As [CAR_CAR#],

date([Date action initiated - 1], 'DD/MM/YYY') as [Date action initiated - 1],

date([Date action initiated - 2], 'DD/MM/YYY') as [Date action initiated - 2],

date([Date action initiated - 3], 'DD/MM/YYY') as [Date action initiated - 3],

date([Date action initiated - 4], 'DD/MM/YYY') as [Date action initiated - 4]

FROM

source

TableX:

CrossTable(Action, A1)

LOAD

[CAR#] As [CAR_CAR#],

[Action - 1],

[Action - 2],

[Action - 3],

[Action - 4]

FROM

source

FinalTable:

NoConcatenate

load

Resident TableD;

Concatenate

load * Resident TableA;

Concatenate

load * Resident TableB;

Concatenate

load * Resident TableC;

Concatenate

load * Resident TableX;

drop tables TableA, TableB, TableC, TableD, TableX;

_____________________________________________________

This is how the data is currently showing but the rows are not linked.

the data should be linked as ActionOwner1 - Owner1 - OpenDate1 - CloseDate1 - ActionType1

Anyone know how I could do this?

Thanks in advance

Re: How to link rows in same table

YoussefBelloum — Wed, 21 Feb 2018 16:30:18 GMT

Hi,

to join all the columns and have your informations in a single line (for each orw), you should use Join instead of concatenate.

you should know what is the joining key between these tables before proceeding.

Read this:

Understanding Join, Keep and Concatenate

Re: How to link rows in same table

Qlik_ULG — Wed, 21 Feb 2018 16:34:22 GMT

Thanks for your reply.

If I join these tables, I get this shown in the table. It does not keep the records unique, when they should be.

I should be only seeing one row for each Action.

Re: How to link rows in same table

YoussefBelloum — Wed, 21 Feb 2018 16:40:08 GMT

how do you want to see one row per Action_type if you have different: action_owner, owner, opendate, closedate and age for every action_type ?

maybe I'm missing something here..

Re: How to link rows in same table

Qlik_ULG — Wed, 21 Feb 2018 16:48:39 GMT

What I am looking for as an end result is this. Something which has clear, unique records for each row & column.

Re: How to link rows in same table

Qlik_ULG — Fri, 23 Feb 2018 15:46:52 GMT

So with the use of cross tables, and hard-coding into each expression, I can see what I need to.

I have to supress null values to only show one record of each unique row.

But this now creates a new problem, as one row has to show NULL as the Action can still be open.

Is there a sort of conditional show nulls I can use?