Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

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

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;

1 Solution

Accepted Solutions
Highlighted
Employee
Employee

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;

View solution in original post

4 Replies
Highlighted
Employee
Employee

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;

View solution in original post

Highlighted
MVP
MVP

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]

;

Highlighted
Specialist III
Specialist III

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;

Highlighted
Specialist
Specialist

Thanks everyone. Jonathan your soln worked perfectly. awesome