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: 
davyqliks
Specialist
Specialist

If statement to avoid duplication of data

Hi All.

I need to avoid a situation where i am loading duplicate data for a [Status] of Both 'Posted' and 'Not Yet Posted'

If possible i need an if line in the load to say:

Load [Invoice no.] if [Status] = 'Not Yet Posted' but if [Status] For [Invoice no.] is duplicate with [Status] of 'Posted' and 'Not Yet Posted' only load Posted.

Basically i can not have an invoice with both status' in the final load.

I will be applying this if load statement to a load from a qvd.  

If anyone can assist in creating the If statement to avoid [Invoice no.] duplication through [Status] it would be very much appreciated.

Thank you in advance.

Regards,

Daniel

Labels (4)
1 Solution

Accepted Solutions
martinpohl
Partner - Master
Partner - Master

the new final script:

Status_temp:

load InvoiceNo,

Status

from qvd (qvd);

Status_Map:

mapping load

InvoiceNo as Number,

Status as Status

resident Status_temp

order by InvoiceNo, Status DESC;

drop table Status_temp;

Status:

load * 

where Status = Overallstatus

;

load *,

applymap('Status_Map',InvoiceNo,null()) as Overallstatus

from your qvd (qvd)

;

View solution in original post

15 Replies
Kushal_Chawda

would you be able to provide some sample data so that we can suggest the better approach

davyqliks
Specialist
Specialist
Author

Hi,

Please inform what you required.

i hope i can explain the need further below:

I have the following fields included:

[Invoice no.]

[Status]

where i am loading multiple files at once, the [Invoice no.] is repeated and in the final document giving the result in the image.

 

In the load i need to allow, 'Not Yet Posted' if this is the only Value and if there is a 'Posted' Value in [Status] only load the 'Posted'

The 'Not Yet Posted' will always be in the data before the 'Posted'.

If you can assist in a load where clause or if statement in the script, that would be great.

Hope this is enough for you to understand

 

davyqliks
Specialist
Specialist
Author

Hi Kush,

Did you have any suggestion on this post?

Is it best to write an If statement associated to [Status] to only load value Posted if both Posted and Not Yet Posted exist?

or

a where clause for Where status = 'Posted','Not Yet Posted' only load the posted Values.

thanks in advance if you get a chance to assist in this.

 

 

martinpohl
Partner - Master
Partner - Master

My suggestion:

Status_Map:

mapping load

InvoiceNo,

Status

from your source

order by InvoiceNo, Status DESC;

So you will get a list of all your invoiceno, the second field is the status, sorted descending. So if invoiceno has postet and not yet posted, posted is the first entry. Otherwise only not yet posted is available.

Then load your datas:

load * 

where Status = Overallstatus

;

load *,

applymap('Status_Map',InvoiceNo,null()) as Overallstatus

from your source

;

Regards

davyqliks
Specialist
Specialist
Author

Thank you for the suggestion Martin,

I have not used Order by before, please can you assist in where that would be placed in the load script?

I have the below but the syntax in incorrect

Status_Map:

mapping load

[Invoice no.],

Status

FROM
[*******************************\SalesLUK.QVD]
(qvd)
Order by [Invoice no.], Status DESC
;

 

Thanks again for your help.

martinpohl
Partner - Master
Partner - Master

That's right, you can't do a "order by" from qvd.

So first load from qvd then mapping load resident qvd.table order by.

Regards

davyqliks
Specialist
Specialist
Author

HI,

Apologies, this is new ground to me here.

So, 

I load like this:

 

Status_Map:

mapping load

[Invoice no.],

Status

resident Sales
Order by [Invoice no.], Status DESC
;

Sales:

LOAD

******


FROM
[*********************************************\SalesLUK.QVD]
(qvd)

WHERE
Status = Overallstatus and
not match (Status,'Cancelled')

 

 

Where do i add the below please? Thanks again

applymap('Status_Map',InvoiceNo,null()) as Overallstatus

Kushal_Chawda

you can do something like below

Data:
Load Invoice,
     Invoice_Line,
     Status
FROM YourQVD;

Final:
NoConcatenate
Load Invoice,
     Invoice as Invoice1,
     Status,
     Invoice_Line
Resident Data
where Status='Posted'; 

Concatenate(Final)
Load *
Resident Data
where not Exists(Invoice1,Invoice);

Drop Table Data;

Drop Field Invoice1;
martinpohl
Partner - Master
Partner - Master

this:

Sales:

LOAD

******


FROM
[*********************************************\SalesLUK.QVD]
(qvd)

WHERE
Status = Overallstatus and
not match (Status,'Cancelled')

you have to change:

Sales:

LOAD

*

WHERE
Status = Overallstatus and
not match (Status,'Cancelled')

;

load *,

applymap(....) as Overallstatus
FROM
[*********************************************\SalesLUK.QVD]
(qvd)

;