Qlikview - show all data from excel

[Anonymous]

Hi all,

i have excel file with two table.

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

and when select in Reason Total i got empty table

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)

Any idea?

[prma7799]

What is your expected output.

[Anonymous]

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

[prma7799]

PFA...

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

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

[Anonymous]

You can achieve this effect by add next selector:

Total in your table is a prat of column reason.

Regards,

Jacek.

[Anonymous]

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

[Kushal_Chawda · Accepted solution]

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.