topic Re: If statement to avoid duplication of data in QlikView

If statement to avoid duplication of data

davyqliks — Sat, 16 Nov 2024 01:29:51 GMT

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

Re: If statement to avoid duplication of data

Kushal_Chawda — Thu, 16 Jan 2020 11:36:16 GMT

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

Re: If statement to avoid duplication of data

davyqliks — Thu, 16 Jan 2020 12:16:45 GMT

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

Re: If statement to avoid duplication of data

davyqliks — Fri, 17 Jan 2020 10:43:44 GMT

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?

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.

Re: If statement to avoid duplication of data

martinpohl — Fri, 17 Jan 2020 10:53:07 GMT

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

Re: If statement to avoid duplication of data

davyqliks — Fri, 17 Jan 2020 11:07:52 GMT

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.

Re: If statement to avoid duplication of data

martinpohl — Fri, 17 Jan 2020 11:11:22 GMT

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

Re: If statement to avoid duplication of data

davyqliks — Fri, 17 Jan 2020 11:37:19 GMT

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

Re: If statement to avoid duplication of data

Kushal_Chawda — Fri, 17 Jan 2020 11:45:05 GMT

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;

Re: If statement to avoid duplication of data

martinpohl — Fri, 17 Jan 2020 11:48:33 GMT

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)

;

Re: If statement to avoid duplication of data

davyqliks — Fri, 17 Jan 2020 12:12:31 GMT

Thank you so much for your time but i am struggling to implement this solution.

Apologies,

Regards,

Re: If statement to avoid duplication of data

davyqliks — Fri, 17 Jan 2020 12:22:36 GMT

HI,

I will try this but first can you try to explain how this will load 'Not Yet Posted' if this is the only status and Only 'Posted' if Both 'Not Yet Posted' and 'Posted' Status exists for the same [Invoice no.]

Thank you for your reply and assistance.

Daniel

Re: If statement to avoid duplication of data

Kushal_Chawda — Fri, 17 Jan 2020 13:37:03 GMT

First load having all the Data

Second load will load the data where status is 'posted' .This will include the invoices where status is only posted. In short it will include invoices where status is both but only posted is loaded and invoices just have posted.

Third load is checking the invoices from second load and loading invoices from first load which are not part of the second load. Hence here we will get invoices with status not posted only. It will not load invoices where status is both because those are already included in second load with posted status.

Finally we are combining second and third load to get all the invoices as required.

Re: If statement to avoid duplication of data

martinpohl — Fri, 17 Jan 2020 15:39:19 GMT

in the mapping table there is every ID and in one line posted and in a second line not yet posted. In another line there is only one line, not yet posted.

An applymap statement tooks the first result for each ID and maps it to the line in the complete records. So for the line with two entries (posted and not posted you will get posted, in the second example not posted). Then in the final load only those lines are loaded where ovall post = actual post, so in example one only one line (posted) is loaded, in the secon example there is only one line which is loaded.

Regards

Re: If statement to avoid duplication of data

martinpohl — Fri, 17 Jan 2020 15:42:28 GMT

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)

;

Re: If statement to avoid duplication of data

davyqliks — Tue, 21 Jan 2020 13:10:07 GMT

HI,

Thank you so much, i have manged something very similar based on this principle:

STORE Sales into *\Temp.QVD
;

Drop Table Sales
;

Temp:

Load

*,[Invoice no.]&'|'& [Invoice item line number] as URN

FROM
[*\Temp.QVD]
(qvd);

STORE Temp into *D\Temp.QVD
;
Drop Table Temp
;

SalesFinal:

Load
*
FROM
[*\Temp.QVD]
(qvd)

Where Status = 'Posted'
;

Concatenate (SalesFinal)

Load

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

Where Status = 'Not Yet Posted'

and

Not Exists

(URN)