Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How can i connect repetition of same rows into one row ?

HELLO COMMUNITY,

Kindly help me to get the desird format of report ..

In attached image i have shown the resultant report but i don't want to repeat the rows with the same story again and again and i want to populate the rows according to the desired pic as i am attaching it as well so please suggest me how can i achieve this task ?

MAINTABLE:

LOAD

    Slug,

    Block,

    hour(time#("Time",'hh:mm:ss:ff')) as [Extracted Hours],

Count(Slug) as "Story Repeat"

FROM

[lib://Desktop/orignalfile.xlsx]

(ooxml, embedded labels, table is Sheet1)

Group by Block,Slug,"Time";

CHILDTABLES:

LOAD

    Slug,

    Block,

    "Extracted Hours",

    if([Extracted Hours]=0,Count([Extracted Hours])) as [00:00 - 01:00],

    if([Extracted Hours]=1,Count([Extracted Hours])) as [01:00 - 02:00],

    if([Extracted Hours]=2,Count([Extracted Hours])) as [02:00 - 03:00],

    if([Extracted Hours]=3,Count([Extracted Hours])) as [03:00 - 04:00] .........

    Count(Slug) as "Story Repeat"

Resident MAINTABLE

Group By Slug,"Extracted Hours",Block;

4 Replies
vinieme12
Esteemed Contributor II

Re: How can i connect repetition of same rows into one row ?

As below

MAINTABLE:

LOAD

    Slug,

    Block,

    hour(time#("Time",'hh:mm:ss:ff')) as [Extracted Hours],

Count(Slug) as "Story Repeat"

FROM

[lib://Desktop/orignalfile.xlsx]

(ooxml, embedded labels, table is Sheet1)

Group by Block,Slug,"Time";

CHILDTABLES:

LOAD

    Slug,

    Block,

Match([Extracted Hours],0) as [00:00 - 01:00],

Match([Extracted Hours],1) as [01:00 - 02:00],

Match([Extracted Hours],2) as [02:00 - 03:00],

Match([Extracted Hours],3) as [03:00 - 04:00],

Count(Slug) as "Story Repeat"

Resident MAINTABLE

Group By Slug,"Extracted Hours",Block;

Expressions

Sum([00:00 - 01:00])

Sum([01:00 - 02:00])

Sum([02:00 - 03:00])

Sum([03:00 - 04:00])

Not applicable

Re: How can i connect repetition of same rows into one row ?

Can you please tell me what is the purpose of Expressions ?

how this will effect in required format

Thanks for contributing

Regards

md_qlikview
Contributor II

Re: How can i connect repetition of same rows into one row ?

Hi,

Can you provide some sample data for testing purpose

vinieme12
Esteemed Contributor II

Re: How can i connect repetition  of same rows into one row ?

have you tried the above approach? please post a sample

Community Browser