Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Table Creation

Hi all,

I need to create a table like below. i have attached the sample .qvf file

The metric column is not a dimension or measure. I need to enter the name is it possible ? if possible how can i do it?

Column To-date : needs the count of invitations, landing page hits, sign-ups, completions from our program start date.

My question is how can i add different dimension, measures for each row and column?

Need to add different measures for each column. is this possible in pivot table?

table.png

Expression to get the above count:

For To-Date column:

1. Invitations : Count({$<DateType = {invitation}>}invitation_id)

2. Landing Page Hits: sum({$<DateType = {accessed}>}landing_page_accessed)

3. Sign-Ups : sum({$<DateType = {signed}>}user_signed)

4. Completions : sum({$<DateType = {completion}>}course_completed)

For Last  Month column:

1. Invitations : count({$<MonthsAgo={">=1<max(MonthsAgo)"}, DateType= {'invitation'}>} invitation_id)

2. Landing Page Hits: sum({$<MonthsAgo={">=1<max(MonthsAgo)"}, DateType= {'accessed'}>} landing_page_accessed)

3. Sign-Ups : sum({$<MonthsAgo={">=1<max(MonthsAgo)"}, DateType= {'signed'}>} user_signed)

4. Completions : sum({$<MonthsAgo={">=1<max(MonthsAgo)"}, DateType= {'completion'}>} course_completed)


As i need to use many measures.

Please help on table creation. I have no idea of creation of table with these many measures.

It's not mandatory to use pivot table just i need the table as above. The .qvf file contains the sample data.

Regards,

Pramod

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Table Creation

Hi,

Modify your script like this with Link table, then it is user to achieve this

SignUp:

LOAD invitation_id,

     user_signed,

     date_signed,

     date_accessed,

     landing_page_accessed

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

CourseCompletion:

LOAD course_completion_id,

     invitation_id,

     lender_offer_id,

     final_test_status,

     percentage_score,

     course_completed

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Invitation:

LOAD invitation_id,

     client_id,

     batch_meta_data_id,

     activated,

     date_activated

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LinkTable:

LOAD DISTINCT invitation_id,

'Landing Page Hits' AS DataType

RESIDENT SignUp;

Concatenate(LinkTable)

LOAD DISTINCT invitation_id,

'Completions' AS DataType

RESIDENT CourseCompletion;

Concatenate(LinkTable)

LOAD DISTINCT invitation_id,

'Invitationa' AS DataType

RESIDENT Invitation;

Concatenate(LinkTable)

LOAD DISTINCT invitation_id,

'SignUps' AS DataType

RESIDENT SignUp;

10 Replies
ink12345
Contributor

Re: Table Creation

Do an inline like below and have a dimension added to your module and it will become a part of your dimension;

Test:

LOAD * Inline [

Metric,

Invitation,

Landing Page Hits

Sign-ups

Completion];

Regards,

Imran K

Not applicable

Re: Table Creation

small clarification the Test: should be the available table or we can have new one

Not applicable

Re: Table Creation

Thanks for the reply,

If i load above script it will not display in column.

please see the imagetable1.png

ink12345
Contributor

Re: Table Creation

Can you please post a sample document?

I have guided you to add dummy dimension and rest you can write in the expression.

Imran K

Not applicable

Re: Table Creation

Anyone help on this........

mto

jagan

gwassenaar

MVP
MVP

Re: Table Creation

Hi,

Can you post some of your sample records so that it would be easier to provide the solution.  or you can try like below

LOAd

*,

Pick(Match(DateType, 'invitation', 'accessed', 'signed', 'completion'), 'Invitations', 'Landing Page Hits', 'Sign-Ups', 'Completions') AS NewField

FROM DataSource;

Now use NewField as dimension in your chart.  Hope this helps you.

Regards,

Jagan.

Not applicable

Re: Table Creation

Hi jagan,

You can find the sample .qvf and i am attaching .csv files also.

Ok i got dimension but how can i use many expressions or measures?

MVP
MVP

Re: Table Creation

Hi,

Modify your script like this with Link table, then it is user to achieve this

SignUp:

LOAD invitation_id,

     user_signed,

     date_signed,

     date_accessed,

     landing_page_accessed

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

CourseCompletion:

LOAD course_completion_id,

     invitation_id,

     lender_offer_id,

     final_test_status,

     percentage_score,

     course_completed

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Invitation:

LOAD invitation_id,

     client_id,

     batch_meta_data_id,

     activated,

     date_activated

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LinkTable:

LOAD DISTINCT invitation_id,

'Landing Page Hits' AS DataType

RESIDENT SignUp;

Concatenate(LinkTable)

LOAD DISTINCT invitation_id,

'Completions' AS DataType

RESIDENT CourseCompletion;

Concatenate(LinkTable)

LOAD DISTINCT invitation_id,

'Invitationa' AS DataType

RESIDENT Invitation;

Concatenate(LinkTable)

LOAD DISTINCT invitation_id,

'SignUps' AS DataType

RESIDENT SignUp;

Not applicable

Re: Table Creation

Thanks a lot for the reply Jagan,

How to create a PIVOT table in standard Qlik Sense

I used this discussion and above your load script

LOAd

*,

Pick(Match(DateType, 'invitation', 'accessed', 'signed', 'completion'), 'Invitations', 'Landing Page Hits', 'Sign-Ups', 'Completions') AS NewField

FROM DataSource;


So now i am able to get the table

Can you help to get the last month, comparison of two months and two years,

you can see in the above image attached two columns YOY(year over year ) and Munthly Run Rate