modify Inline table rows based on value

[berryandcherry6]

Hi,

i have a inline table as below

Metrics:

LOAD * INLINE [

   client_id, Metrics,Metrics-SortOrder

    1, INVITATIONS,1

    1, DELIVERED,2

    1, OPEN,3

    1, BOUNCE,4

    1,CTR,5

    1, SIGN-UP,6

    2, INVITATIONS,1

    2, DELIVERED,2

    2,OPEN,3

    3, DELIVERED,2

    3, OPEN,3

    3, BOUNCE,4

    3,CTR,5

    4, INVITATIONS,1

    4, DELIVERED,2

    4,OPEN,3

];

In above inline table you can see that

1.) Different Client_id has different row

2.) some have like client_id 2 and 5, they have same rows

But above is tedious, if client gets increase number of rows in table get increases and not a right way, i have to make it dynamic or something shorter to achieve same result.

Can i put logical and/or for client_id having same rows?

Any idea in this? or other work around i could achieve. Any suggestions.

[vinieme12]

Sorry, didn't understand what you want to make dynamic

if client gets increase number of rows in table get increases

Are you joining this table to your fact table ??

[hector_munoz]

Hi Supriya,

If several clientes share the same permissions you can create profiles as a set of metrics and assign them to the clients. In this way you only have to create new profiles when necessary and only one record per new client. For example:

Client_Profiles:

LOAD * INLINE [

  client_id, profile_id

  1, PRF1

  2, PRF2

  3, PRF3

  4, PRF2

];

Profiles:

LOAD * INLINE [

  profile_id, Metrics,Metrics-SortOrder

  PRF1, INVITATIONS, 1

  PRF1, DELIVERED, 2

  PRF1, OPEN, 3

  PRF1, BOUNCE, 4

  PRF1, CTR, 5

  PRF1, SIGN-UP, 6

  PRF2, INVITATIONS, 1

  PRF2, DELIVERED, 2

  PRF2, OPEN, 3

  PRF3, DELIVERED, 2

  PRF3, OPEN, 3

  PRF3, BOUNCE, 4

  PRF3, CTR, 5

];

I hope it serves...

Regards,

H

[berryandcherry6]

Hi Vineeth,

Thanks for reply!

No, i wont join this to fact table.

i meant that if other extra clients get added  i.e client_id with 5 and 6. for this clients also i Need to add rows in Inline table.

Can i put logical and/or for client_id having same rows?

[rahulpawarb]

Hello Supriya,

Hope you are doing well!

You can change the layout/structure of INLINE Table to below:

//NOTE: You must sure about no. of Metrics. It will be converted as columns as below.
Metrics:
LOAD * INLINE [
Client_ID, INVITATIONS, DELIVERED, OPEN, BOUNCE, CTR, SIGN-UP
1, 1, 2, 3, 4, 5, 6
2, 1, 2, 3, , ,
3, , 2, 3, 4, 5,
4, 1, 2, 3, , ,
];

Using such structure reduced the no. of rows by 4 times (earlier 16 rows; now 4 rows).

Please let correct me if I misinterpreted the requirement.

Regards!

Rahul

[vinieme12]

Yep you can

Store your Flags like this in a excel sheet and crosstab it during load, simple

CrossTable(MetricType,MetricFlag,1)

LOAD *

FROM

(ooxml, embedded labels, table is Sheet2);

[berryandcherry6]

Hi Vineeth,

Can you put script editor code here, because i am using qliksense

[vinieme12]

I've already posted that above

[berryandcherry6]

Hi Vineeth,

Its syntax is different in Qliksense

CrossTable(MetricType,MetricFlag,1)

LOAD Client_ID, INVITATIONS, DELIVERED, OPEN, BOUNCE, CTR, SIGN-UP

1, 1, 1, 1, 1, 1, 1

2, 1, 1, 1, , ,

3, , 1, 1, 1, 1,

4, 1, 1, 1, , ,

If  i am not having excel file how could i make it to work?

[vinieme12 · Accepted solution]

here's from sense both inline and Excel load, but don't you think a table like this is more readable in excel than inline table??

_______________________________________

using INLINE

------------------------------------------------------------------

CrossTable(MetricType,MetricFlag,1)

LOAD * INLINE [

Client_ID, INVITATIONS, DELIVERED, OPEN, BOUNCE, CTR, SIGN-UP

1, 1, 2, 3, 4, 5, 6

2, 1, 2, 3, , ,

3, , 2, 3, 4, 5,

4, 1, 2, 3, , ,

];

_______________________________________

using Excel

------------------------------------------------------------------

CrossTable(MetricType,MetricFlag,1)

LOAD

    ClientID,

    CTR,

    OPEN,

    INVITATIONS,

    DELIVERED,

    BOUNCE,

    "SIGN-UP"

FROM [lib://FLAGS/MultipleFlags.xlsx]

(ooxml, embedded labels, table is Sheet2);