extracting the master data after using an intersection of master data with smaller data

[mhapanka]

I have a table called tableone to which I had inner joined an external excel sheet because I needed only the intersection of the two data sets for one kind of analyses using the code-

inner Join(tableone)

LOAD

    PROD,

    PC

FROM [lib://AttachedFiles/New file v4.xlsx]

(ooxml, embedded labels, table is Sheet1);

Now for another analyses, I need to make a bar chart using data from tableone wholly- not its intersection with any other dataset. I know I can use qualify or resident load but not sure how to go about it. Could someone help me with the code for this please? And would I need to use any filters to tell qlik that the whole table tableone has to be taken?

Thanks in advance

[vamsee · Accepted solution]

Hi Mallika,

Instead of an inner join use Applymap() and create a flag.

Use that flag to work on the charts which represent inner join and your source data stays intact for the rest of the charts.

Like

Table2_Map:

Mapping load

    PROD,

    1 as Flag

FROM [lib://AttachedFiles/New file v4.xlsx]

(ooxml, embedded labels, table is Sheet1);

Tableone:

LOAD *,

Applymap('Table2_Map', PROD, 0) as Flag

from Source;

[ajsjoshua]

Dear Mallika,

You can also do this by without joining both tables.

Tableone:

LOAD *

from Source;

Table3:

Load distinct

key field  as Existskey

Resident Tableone;

Table2:

LOAD

    PROD,

    PC

FROM [lib://AttachedFiles/New file v4.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where exists(Existskey,[Your key field PROD or PC]);

Drop table Table3;

[mhapanka]

The problem is that my tableone has a lot of data transformations and I would like to keep the join and then use tableone without the join,.

Is that possible?

[mhapanka]

The problem is that my tableone has a lot of data transformations and I would like to keep the join and then use tableone without the join,.

Is that possible?

[vamsee]

I am sorry I couldn't understand. Table one has data transformations based on Columns from Table 2?

And then revert to the original state (before join)?

If that is the Case, then you might have to use Qualify and create a data island for this particular chart.

[mhapanka]

tableone has transformations that I need to keep for every analyses. I just want to make a bar chart now that does not use the inner join of the excel file to tableone.

Basicalllly tableone extracts- 690k lines

after joining the intersection with the excel file has 30k lines'

for my last analyses, i need the original 690k lines.

what code should I use for this

[vamsee]

Are your transformations based on 690k lines or 30k lines.

1. If you want to do your transformations on 30k lines then you might have to load the 690K lines again using Qualify and UnQualify.

Do you Qualify?- How to use QUALIFY statement

2. If you are okay with doing your transformations on 690K lines and use the flag for restricting data to 30K in charts then use the previously suggested approach.

3. One more option you can try is creating an aggregated table with columns relevant to bar chart in the script itself and outer join to the original table with relevant keys.

Eg: Bar Chart

                     Dim: PROD, Year                 

                     Exp: Sum(Sales)

Your Aggregated table should be

Aggr_Table:

Load

     PROD,

     Year as AGGR_Year,

     Sum(Sales) as AGGR_Sales

Resident TableOne

GroupBy PROD, Year;

After this, do the inner join of TableOne and Table2