Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pra_kale
Creator III
Creator III

Report in specific format Required

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_noDate_1Amt_1Date_2Amt_2
1234567810-Sep-15 12-Sep-15
567891005-Sep-15 08-Sep-15
234567812-Sep-15 14-Sep-15
110890101-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.

1 Solution

Accepted Solutions
sunny_talwar

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:

Capture.PNG

View solution in original post

6 Replies
Not applicable

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;


Capture.PNG

pra_kale
Creator III
Creator III
Author

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

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:

Capture.PNG

pra_kale
Creator III
Creator III
Author

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_noDate_1Amt_1Date_2Amt_2
1234567810-Sep-1501-Sep-15
567891005-Sep-1508-Sep-15
234567812-Sep-1501-Aug-15
110890101-Oct-1510-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.