Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How show same data twice in table?

I've got a pivot table with the below structure, showing tasks per country and year created. The year is gotten with dimension "=Year(CreationDate). The count is done with expression "=Count(ID)".

Country / Year201120122013
Sweden242
Norway324
Finland225

Some tasks have a status called "Cancelled". Now I wish to show these both under the year created AND under the year cancelled, see example below (ie. the 2 showing as Cancelled also appear under the year they were created).

Country / Year201120122013
Status

Created

CancelledCreatedCancelledCreatedCancelled
Sweden242
Norway3242
Finland225

I'm not sure how to accomplish this. I can easily do a sum of those with cancelled status, but they then show under the year created. I want them to remain there but also that they show under the year cancelled. Can anyone help with this please?

Attachment added

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Re: Re: Re: How show same data twice in table?

Hi Gustav,

Sorry for misunderstanding. Anyway, please see attached file again, or the script below:

/***********************************/

Table1:

LOAD * INLINE [

    Country, CreationDate, CancelDate, ID, Status

    Sweden, 2011, , 1

    Sweden, 2011, , 2

    Sweden, 2012, , 3

    Sweden, 2012, , 4

    Sweden, 2012, , 5

    Sweden, 2012, , 6

    Sweden, 2013, , 7

    Sweden, 2013, , 8

    Norway, 2011, , 9

    Norway, 2011, , 10

    Norway, 2011, 2013, 11, Cancelled

    Norway, 2012, , 12,

    Norway, 2012, 2013, 13, Cancelled

    Norway, 2013, , 14

    Norway, 2013, , 15

    Norway, 2013, , 16

    Norway, 2013, , 17

    Finland, 2011, , 18

    Finland, 2011, , 19

    Finland, 2012, , 20

    Finland, 2012, , 21

    Finland, 2013, , 22

    Finland, 2013, , 23

    Finland, 2013, , 24

    Finland, 2013, , 25

    Finland, 2013, , 26

];

NoConcatenate

NewTable:

/*For Created ID*/

Load Country,

  CreationDate as Year,

  ID,

  'Created' as Status

Resident Table1;

Concatenate(NewTable)

/*For Cancelled ID*/

Load Country,

  CancelDate as Year,

  ID,

  'Cancelled' as Status

Resident Table1 where Status = 'Cancelled';

DROP Table Table1;

/***********************************/

Hope this helps! Thank you and good luck!

Best Regards,

Bill

View solution in original post

10 Replies
Highlighted
Specialist III
Specialist III

Re: How show same data twice in table?

Did you try using Status as the third dimension?

Highlighted
Not applicable

Re: How show same data twice in table?

Yes, I did. But then the data still show up under date created as this is the first dimension I'm using. I need it to show both under date created AND date cancelled.

Thanks for your answer still.

Highlighted
MVP & Luminary
MVP & Luminary

Re: How show same data twice in table?

Hi,

Can you attach some sample data and qlikview file.

Regards,

Jagan.

Highlighted
Not applicable

Re: Re: How show same data twice in table?

Good idea. I've included an attachment. So how can I make the table in it look like the second table in my question?

Highlighted
Partner
Partner

Re: Re: Re: How show same data twice in table?

Hmm please see attached file. Hope it helps.

Highlighted
Not applicable

Re: Re: Re: How show same data twice in table?

Thank you for this. Unfortunately, that shows the cancelled tasks under year created. I need to show them under the year they were cancelled.

These two things need to be shown, so that each year shows how many tasks were created and how many were cancelled:

  1. Show all tasks grouped by year created (CreationDate).
  2. Show cancelled tasks by year cancelled (CancelDate).
Highlighted
Partner
Partner

Re: Re: Re: Re: How show same data twice in table?

Hi Gustav,

Sorry for misunderstanding. Anyway, please see attached file again, or the script below:

/***********************************/

Table1:

LOAD * INLINE [

    Country, CreationDate, CancelDate, ID, Status

    Sweden, 2011, , 1

    Sweden, 2011, , 2

    Sweden, 2012, , 3

    Sweden, 2012, , 4

    Sweden, 2012, , 5

    Sweden, 2012, , 6

    Sweden, 2013, , 7

    Sweden, 2013, , 8

    Norway, 2011, , 9

    Norway, 2011, , 10

    Norway, 2011, 2013, 11, Cancelled

    Norway, 2012, , 12,

    Norway, 2012, 2013, 13, Cancelled

    Norway, 2013, , 14

    Norway, 2013, , 15

    Norway, 2013, , 16

    Norway, 2013, , 17

    Finland, 2011, , 18

    Finland, 2011, , 19

    Finland, 2012, , 20

    Finland, 2012, , 21

    Finland, 2013, , 22

    Finland, 2013, , 23

    Finland, 2013, , 24

    Finland, 2013, , 25

    Finland, 2013, , 26

];

NoConcatenate

NewTable:

/*For Created ID*/

Load Country,

  CreationDate as Year,

  ID,

  'Created' as Status

Resident Table1;

Concatenate(NewTable)

/*For Cancelled ID*/

Load Country,

  CancelDate as Year,

  ID,

  'Cancelled' as Status

Resident Table1 where Status = 'Cancelled';

DROP Table Table1;

/***********************************/

Hope this helps! Thank you and good luck!

Best Regards,

Bill

View solution in original post

Highlighted
MVP
MVP

Re: Re: Re: Re: How show same data twice in table?

Hi

See attached using a date island and count(if()) logic to show the data the way you want,

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Not applicable

Re: Re: Re: Re: How show same data twice in table?

Excellent, thank very much you for your swift reply! I'll remember to explain more clearly next time 🙂