Report in specific format Required

[pra_kale]

Hi All,

I am attaching Two sample excel files in one excel file i.e. Format_Require which is given below and this format I want my final out-put.

Policy_no	Date_1	Amt_1	Date_2	Amt_2
12345678	10-Sep-15		12-Sep-15
5678910	05-Sep-15		08-Sep-15
2345678	12-Sep-15		14-Sep-15
1108901	01-Oct-15		10-Oct-15

In the second excel file I have Policyno,Date,Amt.

I have trying by writing below given script but below given script is not giving desired result.

Directory;

LOAD Policy_no,

     Date_1,

     Amt_1,

     Date_2,

     Amt_2,

     Policy_no & Date_1 as key1    

FROM

Format_Require.xlsx

(ooxml, embedded labels, table is Sheet1);

left Join

LOAD Policyno,

     Date,

     Amt,

    Policyno &  Date as key1

FROM

Amt.xlsx

(ooxml, embedded labels, table is Sheet1);

Is there any by which I can get desired result.

Thanks in Advance.

[#2]

Hi, could you more accurately describe what you want to accomplish.

regards

SGI

[sunny_talwar]

Try this:

Table:

LOAD Policyno,

    Date,

    Amt

FROM

Amt.xlsx

(ooxml, embedded labels, table is Sheet1);

Count:

LOAD Policyno,

  Count(Date) as Count

Resident Table

Group By Policyno;

MaxCount:

LOAD Max(Count) as MaxCount

Resident Count;

LET vMaxCount = Peek('MaxCount');

DROP Table Count, MaxCount;

LET vJoin = '';

FOR i = 1 to $(vMaxCount)

  FinalTable:

  $(vJoin)

  LOAD Policyno,

  Date(Min(Date, $(i))) as Date$(i),

  FirstSortedValue(Amt, Date, $(i)) as Amt$(i)

  Resident Table

  Group By Policyno;

  LET vJoin = 'Join(FinalTable)';

// TRACE $(i);

NEXT

DROP Table Table;

[pra_kale]

Hi,

Thanks for your reply and help.

But when I copy paste your code in the script it gives me an error after For loop  which is given below..I think some syntax error

FinalTable:

   $(vJoin)

  LOAD Policyno,

  Date(Min(Date, $(i))) as Date$(i),

  FirstSortedValue(Amt, Date, $(i)) as Amt$(i)

  Resident Table

  Group By Policyno;

shall I need to make any changes at my end...

secondly, by looking at the out-put which you have shared , this code might be giving results for all the dates from Amt excel sheet but I want results only for the dates mentioned in the  Format_Require.xlsx.

for e.g. In case of policy no 12345678 I want the out-put only for 10-Sep-2015 and 12-Sep-2015...and not for 1-Sep-2015.

Thanks in advance.

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD Policyno,

    Date(Max(Date, 1), 'DD-MMM-YYYY') as Date2,

    FirstSortedValue(Amt, -Date, 1) as Amt2

FROM

Amt.xlsx

(ooxml, embedded labels, table is Sheet1)

Group By Policyno;

Join(Table)

LOAD Policyno,

    Date(Max(Date, 2), 'DD-MMM-YYYY') as Date1,

    FirstSortedValue(Amt, -Date, 2) as Amt1

FROM

Amt.xlsx

(ooxml, embedded labels, table is Sheet1)

Group By Policyno;

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Len(Trim(Date1)) > 0;

DROP Table Table;

Output:

[pra_kale]

Hi,

Thank you vary much.. In current scenario the out-put is perfectly OK...

But as this is just e.g. and  in reality I want the out-put as per the Format_Require sheet and in that I may get the cases where out-put between Two dates also require In that case how to handle ..can you pleases help in this as well...

like below given e.g. If  I require the out-put.

Policy_no	Date_1	Amt_1	Date_2	Amt_2
12345678	10-Sep-15		01-Sep-15
5678910	05-Sep-15		08-Sep-15
2345678	12-Sep-15		01-Aug-15
1108901	01-Oct-15		10-Oct-15

Thanks in advance.

[sunny_talwar]

I am not able to follow the logic you are trying to implement? We cannot do random things in QlikView,I would need to know how would you decide which Date is Date_1 and which one is Date_2.