topic Re: Need a Help to Resolve Circular Loop in QlikView

Need a Help to Resolve Circular Loop

SatyaPaleti — Sat, 16 Nov 2024 21:31:47 GMT

Hi Folks,

Need your help to resolve circular loop. I am having 3 tables

1 Daily Table

2 Segment Table

3 Budget Table

here is the code

Daily:
LOAD [Agent No],
[Issue Date],
Month([Issue Date]) as [Issue Month]
FROM
C:\Users\Home\Desktop\Test_Date.xls
(biff, embedded labels, table is [Daily$]);

Segment:
LOAD Segment,
[Agent No]
FROM
C:\Users\Home\Desktop\Test_Date.xls
(biff, embedded labels, table is [Segment$]);

Budget:
LOAD Segment,
Budget,
Month
FROM
C:\Users\Home\Desktop\Test_Date.xls
(biff, embedded labels, table is [Budget$]);

In Daily file we have Month & in Budget also we have Month. So my requirement is when ever there is [Issue Month] got selected then same month should select in Month in Budget table.

For example if i select Jan Month in Daily Table then Same value should get select in Month(Value is Jan) in Budget Table. Could you please find attached sample qvw and sample data and Please help to resolve this Issue

Thanks and Regards,

Satya

Daily table has Date and

Re: Need a Help to Resolve Circular Loop

m_woolf — Mon, 28 Jan 2019 12:46:10 GMT

You can use an OnSelect field event triggers:

For the Month Field, add the action to Select in Field: Issue Month value equals Month

Create the save trigger for the Month field value equals Issue Month

Re: Need a Help to Resolve Circular Loop

nwest1965 — Mon, 28 Jan 2019 12:55:30 GMT

Seems like one agent has only one segment, in which case I would left join the segment table to the Daily table and then make a composite key to join to budget.

TMP:
LOAD [Agent No],
[Issue Date],
Month([Issue Date]) as [Issue Month]
FROM C:\Users\Home\Desktop\Test_Date.xls
(biff, embedded labels, table is [Daily$]);

Left Join (TMP)
LOAD Segment,
[Agent No]
FROM C:\Users\Home\Desktop\Test_Date.xls
(biff, embedded labels, table is [Segment$]);

You should now have a table (TMP) with following fields:

Agent No; Issue Date; Issue Month; Segment

We now create a new copy of the table with a composite key:

DAILY:

NoConcatenate

LOAD *, Autonumber(Segment & Month) AS Key1 RESIDENT TMP;

DROP TABLE TMP;

And finally, load the budget table, but only load the Segment & Month as a key, not as fields:

Budget:

LOAD Budget, Autonumber(Segment & Month) AS Key1

FROM BudgetTableFile

This Solution won't work if you have multiple segments per agent.