Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

modify Inline table rows based on value

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.

1 Solution

Accepted Solutions
vinieme12
Champion II
Champion II

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);

View solution in original post

12 Replies
vinieme12
Champion II
Champion II

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
Specialist
Specialist

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
Creator II
Creator II
Author

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
Specialist III
Specialist III

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
Champion II
Champion II

Yep you can

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

flag.PNG

CrossTable(MetricType,MetricFlag,1)

LOAD *

FROM

(ooxml, embedded labels, table is Sheet2);

berryandcherry6
Creator II
Creator II
Author

Hi Vineeth,

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

vinieme12
Champion II
Champion II

I've already posted that above

berryandcherry6
Creator II
Creator II
Author

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
Champion II
Champion II

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);

View solution in original post