Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
777proba777
New Contributor III

Qlikview - show all data from excel

Hi all,

i have excel file with two table.

Untitled.png

I write in script code like this

  Data:

CrossTable(Location, Quantity)

LOAD Reason,

    LocA,

    LocB,

  DateM

FROM

(ooxml, embedded labels, table is Table1);

LOAD Location,

    Re as Reason,

    Answer,

    Date

FROM

(ooxml, embedded labels, table is Table2);


And I get table


Untitled1.png


and when select in Reason Total i got empty table

Untitled2.png


The first table is ok when i choose R1,R2... but how to chenge code when press Total in Reason to get all Location and it's data depending on date. Something like this (if choose Reason Total and Date 08-05-17)

Untitled3.png

Any idea?

Tags (3)
1 Solution

Accepted Solutions

Re: Qlikview - show all data from excel

try this

Data:

CrossTable(Location,Quantity,2)

LOAD Reason,

     Reason as ReasonFilter,

     DateM as Date,

     LocA,

     LocB

FROM

(ooxml, embedded labels, table is Table1, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'Total')))

));

Left Join(Data)

LOAD Location,

     Re as Reason,

     Answer,

     Date

FROM

(ooxml, embedded labels, table is Table2);

Concatenate(Data)

LOAD  Reason,

      Location,

      Quantity,

     'Total' as ReasonFilter,

      Date,

      Re,

     Answer

Resident Data;

Use ReasonFilter as Filter


Note:

You need to write distinct key word in expression to show values as here data is getting duplicate


Update: I am correcting the mistake here so that correct answer should look correct.

13 Replies
prma7799
Honored Contributor III

Re: Qlikview - show all data from excel

What is your expected output.

777proba777
New Contributor III

Re: Qlikview - show all data from excel

The last image, If select Total for date 08-05-17 to get all data (Location,Resaon,Answer...) related to this date.

prma7799
Honored Contributor III

Re: Qlikview - show all data from excel

PFA...

777proba777
New Contributor III

Re: Qlikview - show all data from excel

This is not that. You don't have in  list Reason value Total... I must to have Total and when press Total depending of date table fill with data....

jaceknoden
Contributor

Re: Qlikview - show all data from excel

Hello Friend,

Data:

CrossTable(Location, Quantity,3)

  LOAD Reason ,

  DateM,

  Reason &'-'& DateM as KEY,

    LocA,

    LocB

FROM

(ooxml, embedded labels, table is Table1);

LOAD

    Re &'-' & Date as KEY,

    Answer

FROM

(ooxml, embedded labels, table is Table2);

Rest in qvw.

Regards

Jacek.

777proba777
New Contributor III

Re: Qlikview - show all data from excel

Thanks, but this is not what i want. When i run your code i got this

Untitled4.png

i want when I choose Total and date to get from second table rows that contains choosen date like in image

Untitled3.png

jaceknoden
Contributor

Re: Qlikview - show all data from excel

You can achieve this effect by add next selector:

Total in your table is a prat of column reason.

sample.png

Regards,

Jacek.

777proba777
New Contributor III

Re: Qlikview - show all data from excel

I now, but i have task when press Total to show that table...

Re: Qlikview - show all data from excel

try this

Data:

CrossTable(Location,Quantity,2)

LOAD Reason,

     Reason as ReasonFilter,

     DateM as Date,

     LocA,

     LocB

FROM

(ooxml, embedded labels, table is Table1, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'Total')))

));

Left Join(Data)

LOAD Location,

     Re as Reason,

     Answer,

     Date

FROM

(ooxml, embedded labels, table is Table2);

Concatenate(Data)

LOAD  Reason,

      Location,

      Quantity,

     'Total' as ReasonFilter,

      Date,

      Re,

     Answer

Resident Data;

Use ReasonFilter as Filter


Note:

You need to write distinct key word in expression to show values as here data is getting duplicate


Update: I am correcting the mistake here so that correct answer should look correct.