My table is getting dropped. What is wrong in the script

[Anonymous]

I have the following script. what i am trying to achieve is first add a field with max date and then load only those lines that have a max date. but all fields and tables keep getting dropped. Dont know what is the problem.

temp1:

LOAD User,

     [Cell Phone Plan],

     [Start Date],

     [End Date]

FROM

Excel;

temp2:

left join(temp1)

load User,

     max(date([End Date])) as max_end_dt

     Resident temp1

     Group by User;

Final:

load User,

     [Cell Phone Plan],

     [Start Date],

     [End Date],

     max_end_dt

     Resident temp1

     where [End Date]=max_end_dt;

     drop table temp1;

[JonnyPoole · Accepted solution]

Try using noconcatenate to prevent auto-concatenation (because temp1 has the same number and named fields as final) :

noconcatenate

Final:

load User,

     [Cell Phone Plan],

     [Start Date],

     [End Date],

     max_end_dt

     Resident temp1

     where [End Date]=max_end_dt;

drop table temp1;

[petter]

How about:

LOAD

    User,

    [Cell Phone Plan],

    Min([Start Date]) As StartDate,

    Max([End Date]) As EndDate

FROM

Excel

Group By

    User,[Cell Phone Plan]

;

[ramoncova06]

the issue is that since your two tables have the same fields qlikview applies a natural concatenation, using prefix NoConcatenate to your final table could correct this another option is to use a map instead of a left join

temp1:

LOAD User,

     [Cell Phone Plan],

     [Start Date],

     [End Date]

FROM

Excel;

mapping

mapmaxdate:

load User,

     max(date([End Date])) as max_end_dt

     Resident temp1

     Group by User;

Final:

load User,

     [Cell Phone Plan],

     [Start Date],

     [End Date],

     ('mapmaxdate',User) as max_end_dt

     Resident temp1

     where applymap('mapmaxdate',User,0) <> 0 ;

     drop table temp1;

[Anonymous]

Thanks everyone. Jonathan your soln worked perfectly. awesome