topic Re: Qlikview - show all data from excel in QlikView

Qlikview - show all data from excel

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Qlikview - show all data from excel

prma7799 — Fri, 26 May 2017 10:42:25 GMT

What is your expected output.

Re: Qlikview - show all data from excel

Anonymous — Fri, 26 May 2017 10:44:35 GMT

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

Re: Qlikview - show all data from excel

prma7799 — Fri, 26 May 2017 10:50:46 GMT

PFA...

Re: Qlikview - show all data from excel

Anonymous — Fri, 26 May 2017 10:54:06 GMT

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

Re: Qlikview - show all data from excel

Anonymous — Fri, 26 May 2017 10:59:36 GMT

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.

Re: Qlikview - show all data from excel

Anonymous — Fri, 26 May 2017 11:23:08 GMT

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

Re: Qlikview - show all data from excel

Anonymous — Fri, 26 May 2017 11:33:23 GMT

You can achieve this effect by add next selector:

Total in your table is a prat of column reason.

Regards,

Jacek.

Re: Qlikview - show all data from excel

Anonymous — Fri, 26 May 2017 11:38:47 GMT

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

Re: Qlikview - show all data from excel

Kushal_Chawda — Fri, 26 May 2017 11:46:21 GMT

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.

Re: Qlikview - show all data from excel

Anonymous — Fri, 26 May 2017 12:09:03 GMT

It's not well connected.

For LocA and R1 i have only answer Yes and value 5. ... but I got to many combination...

Re: Qlikview - show all data from excel

Anonymous — Fri, 26 May 2017 12:13:38 GMT

I add this in second table Re as Reason and worrk,

THAKS A LOT!!!

Re: Qlikview - show all data from excel

Kushal_Chawda — Fri, 26 May 2017 12:20:54 GMT

my mistake

Data:

CrossTable(Location,Quantity,2)

LOAD Reason,

Reason as ReasonFilter,

DateM as Date,

LocA,

LocB

FROM

[Book1.xlsx]

(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

[Book1.xlsx]

(ooxml, embedded labels, table is Table2);

Concatenate(Data)

LOAD Reason,

Location,

Quantity,

'Total' as ReasonFilter,

Date,

Answer

Resident Data;

Re: Qlikview - show all data from excel

Kushal_Chawda — Fri, 26 May 2017 12:22:06 GMT

Great