topic Re: Add a column in a table from the script in QlikView

Add a column in a table from the script

Wed, 18 Nov 2015 08:19:37 GMT

Hello,

I have 2 tables which i read from SAP: EKKO and EKPO.

EKKO contains the headers of the orders and EKPO contains all the lines in the orders.

Here is the thing, i want to add a column in EKKO depending on the value of a column in EKPO.

There is my EKKO table:

Order	Date
1	18.11.2015
2	19.11.2015
3	20.11.2015

There is my EKPO table:

Order	Status	Quantity
1	X	1
1		2
2	X	3
2	X	4
2	X	5
3	X	6

And there is the EKKO table i'm looking for:

Order	Date	Status
1	18.11.2015
2	19.11.2015	X
3	20.11.2015	X

For the same Order,

If Status = 'X' for each EKPO line

Add a column Status in EKKO with the value 'X'

How can i do this please?

This is how i loop the EKPO table but i have no idea how to do the rest...

LET vNumRows=NoOfRows('EKPO');

FOR i=1 to $(vNumRows)

LET vStatus=Peek('Status',$(i));

//do the stuff

NEXT;

Thank you very much for your responses,

Best Regards,

Loïc

Re: Add a column in a table from the script

Gysbert_Wassenaar — Wed, 18 Nov 2015 08:58:19 GMT

Maybe like this:

EKKO:

LOAD * FROM EKKO;

EKPO:

LOAD * FROM EXPO;

JOIN(EKKO)

LOAD Order, FirstSortedValue(Status, -Quantity) as Status

Resident EKPO

GROUP BY Order;

Re: Add a column in a table from the script

Wed, 18 Nov 2015 09:16:42 GMT

This could work for my little example but not for my whole table.

This is not what i want, you just sort EKPO by the Quantity and take the Status of the bigger to put in EKKO.

I have to watch the Status of every lines in EKPO and only if all the lines have Status 'X' for an Order, put 'X' in the column Status from EKKO.

Re: Add a column in a table from the script

Gysbert_Wassenaar — Wed, 18 Nov 2015 09:27:48 GMT

EKKO:

LOAD * FROM EKKO;

EKPO:

LOAD

Order,

If(len(trim(Status))=0;'N/A',Status) as Status

FROM EXPO;

JOIN(EKPO)

LOAD Order, Count(distinct Status) as StatusCount

Resident EKPO

GROUP BY ORder;

JOIN(EKKO)

LOAD distinct Order, Status

Resident EKPO

WHERE StatusCount = 1 and Status = 'X';

Re: Add a column in a table from the script

Wed, 18 Nov 2015 10:20:04 GMT

JOIN(EKPO)

LOAD Order, Count(distinct Status) as StatusCount

JOIN(EKKO)

LOAD distinct Order, Status

Resident EKPO

WHERE StatusCount = 1 and Status = 'X';

I think you missed something there.

Re: Add a column in a table from the script

Gysbert_Wassenaar — Wed, 18 Nov 2015 10:28:32 GMT

Yeah, you're right. Fixed now.

Re: Add a column in a table from the script

Wed, 18 Nov 2015 10:35:20 GMT

Hmm yeah that's what i thought...

I got this error message:

"Invalid expression

JOIN(EKPO)

LOAD Order, Count(distinct Status) as StatusCount Resident EKPO"

I think you can't JOIN a table and LOAD from this same table.

Re: Add a column in a table from the script

Gysbert_Wassenaar — Wed, 18 Nov 2015 10:48:40 GMT

See attached example.