topic Re: Assigning Separate Day for Quantity and VALUE in QlikView

Assigning Separate Day for Quantity and VALUE

munnawar — Wed, 25 Nov 2020 16:16:04 GMT

HI Everone,

i Have data for Each day as Quantity n Value in QVD , I separated data for Quantity and value using CROSS JOIN n creating separate QVD,

and loading them separately . Either if i CONCATENATE or JOIN both QVD's , My data is Dividing Day in two Separate day for Quantity and Value

but i want in Single row with single day

for Ex

Day QTY value

1 10 -

1 - 50

2 20 -

2 - 100

But I need

Day QTY Value

1 10 50

2 20 100

Please help me out

Thanks

Re: Assigning Separate Day for Quantity and VALUE

sunny_talwar — Mon, 11 Dec 2017 16:19:13 GMT

What is the script that you use?

Re: Assigning Separate Day for Quantity and VALUE

trdandamudi — Mon, 11 Dec 2017 17:13:54 GMT

One way is as below:

Data:
Load * inline [
Day,QTY,value
1,10
1, ,50
2, 20
2, ,100
];

NoConcatenate
Final:
Load Day,Sum(QTY) as QTY,
Sum(value) as value
Resident Data
Group By Day;

Drop table Data;

Re: Assigning Separate Day for Quantity and VALUE

munnawar — Tue, 12 Dec 2017 05:32:05 GMT

Hi Sunny ,

The DATA in QVD is storing DAY wise for QTY and VALUE and I Sliced DATA into separate QVD using CROSSTABLE n Loading this below script

The script is like this , Yet if i introduce Dummy column for QTY & VALUE , it is showing same output .

ZMM_INVENTORY:

LOAD

Material_MATNR,

Plant_WERKS,

Month_MONYR,

Day,

num(Day,'00')&'/'&right(Month_MONYR,2)&'/'&left(Month_MONYR,4) as Date,

QTY as InventoryQty,

Join

LOAD

Material_MATNR,

Plant_WERKS,

Month_MONYR,

Day,

num(Day,'DD')&'/'&right(Month_MONYR,2)&'/'&left(Month_MONYR,4) as Date,

VALUE as InventoryValue

This Days like 1,1,2,2,3,3 its is repeating n giving me separate QTY & VALUE

Re: Assigning Separate Day for Quantity and VALUE

munnawar — Tue, 12 Dec 2017 06:03:21 GMT

Hi Thirumala thanks for feedback,

Im not using INLINE LOAD ,Data is coming from QVD's, i showed only for reference , i am describing below plz let me know

The DATA in QVD is storing DAY wise for QTY and VALUE and I Sliced DATA into separate QVD using CROSSTABLE n Loading this below script

The script is like this , Yet if i introduce Dummy column for QTY & VALUE , it is showing same output .

ZMM_INVENTORY:

LOAD

Material_MATNR,

Plant_WERKS,

Month_MONYR,

Day,

num(Day,'00')&'/'&right(Month_MONYR,2)&'/'&left(Month_MONYR,4) as Date,

QTY as InventoryQty,

Join

LOAD

Material_MATNR,

Plant_WERKS,

Month_MONYR,

Day,

num(Day,'DD')&'/'&right(Month_MONYR,2)&'/'&left(Month_MONYR,4) as Date,

VALUE as InventoryValue

This Days like 1,1,2,2,3,3 its is repeating n giving me separate QTY & VALUE

Re: Assigning Separate Day for Quantity and VALUE

cweiping — Tue, 12 Dec 2017 07:48:38 GMT

try it.

ZMM_INVENTORY_N:

NoConcatenate

Final:

Load

Material_MATNR,

Plant_WERKS,

Month_MONYR,

Day,

Date,

,Sum(InventoryQty) as InventoryQty,,

Sum(InventoryValue) as InventoryValue

Resident ZMM_INVENTORY

Group By

Material_MATNR,

Plant_WERKS,

Month_MONYR,

Day,

Date;

Drop table ZMM_INVENTORY;

Re: Assigning Separate Day for Quantity and VALUE

sunny_talwar — Tue, 12 Dec 2017 09:57:43 GMT

What is the source of both these two loads? Is it a same file or different source?

Re: Assigning Separate Day for Quantity and VALUE

munnawar — Tue, 12 Dec 2017 12:09:54 GMT

Hi Sunny,

The first Source is QVD which has data as MaterialNor,Plant,MonthYear this three coloumn Next colomn's are Day wise data like DAY1 Quantity,DAY1 Value,DAY2 Quantity,DAY2 Value .... so on.

This QVD i have separated for Value and Quantity into two different QVD's using CROSSTABLE and finally

Concatenate

Re: Assigning Separate Day for Quantity and VALUE

sunny_talwar — Tue, 12 Dec 2017 12:30:54 GMT

So the source for both guys are the same qvd?... would you be able to share the script you used to create the QVD (where you used CROSSTABLE and concatenate)

Re: Assigning Separate Day for Quantity and VALUE

stabben23 — Tue, 12 Dec 2017 12:31:52 GMT

Hi,

where do Field containing C102021B comes from? if you "delete" that Field in Your table, will you then have it on one row?

Re: Assigning Separate Day for Quantity and VALUE

munnawar — Wed, 13 Dec 2017 10:00:56 GMT

Hi Sunny ,

Im Pasted entire script as below

ZMM_QTY:

LOAD

Material_MATNR,

Plant_WERKS,

Month_MONYR,

Quantity_DAY1_QTY as 1,

Quantity_DAY2_QTY as 2,

Quantity_DAY3_QTY as 3,

Quantity_DAY4_QTY as 4,

Quantity_DAY5_QTY as 5,

Quantity_DAY6_QTY as 6,

Quantity_DAY7_QTY as 7,

Quantity_DAY8_QTY as 8,

Quantity_DAY9_QTY as 9,

Quantity_DAY10_QTY as 10,

Quantity_DAY11_QTY as 11,

Quantity_DAY12_QTY as 12,

Quantity_DAY13_QTY as 13,

Quantity_DAY14_QTY as 14,

Quantity_DAY15_QTY as 15,

Quantity_DAY16_QTY as 16,

Quantity_DAY17_QTY as 17,

Quantity_DAY18_QTY as 18,

Quantity_DAY19_QTY as 19,

Quantity_DAY20_QTY as 20,

Quantity_DAY21_QTY as 21,

Quantity_DAY22_QTY as 22,

Quantity_DAY23_QTY as 23,

Quantity_DAY24_QTY as 24,

Quantity_DAY25_QTY as 25,

Quantity_DAY26_QTY as 26,

Quantity_DAY27_QTY as 27,

Quantity_DAY28_QTY as 28,

Quantity_DAY29_QTY as 29,

Quantity_DAY30_QTY as 30,

Quantity_DAY31_QTY as 31

FROM

(qvd);

store ZMM_QTY into E:\DAILY INVENTORY\ZMM_QTY.qvd(qvd);

DROP Table ZMM_QTY;

ZMM_VALUE:

LOAD

Material_MATNR,

Plant_WERKS,

Month_MONYR,

[Amount in LC_DAY1_VAL] as 1,

[Amount in LC_DAY2_VAL] as 2,

[Amount in LC_DAY3_VAL] as 3,

[Amount in LC_DAY4_VAL] as 4,

[Amount in LC_DAY5_VAL] as 5,

[Amount in LC_DAY6_VAL] as 6,

[Amount in LC_DAY7_VAL] as 7,

[Amount in LC_DAY8_VAL] as 8,

[Amount in LC_DAY9_VAL] as 9,

[Amount in LC_DAY10_VAL] as 10,

[Amount in LC_DAY11_VAL] as 11,

[Amount in LC_DAY12_VAL] as 12,

[Amount in LC_DAY13_VAL] as 13,

[Amount in LC_DAY14_VAL] as 14,

[Amount in LC_DAY15_VAL] as 15,

[Amount in LC_DAY16_VAL] as 16,

[Amount in LC_DAY17_VAL] as 17,

[Amount in LC_DAY18_VAL] as 18,

[Amount in LC_DAY19_VAL] as 19,

[Amount in LC_DAY20_VAL] as 20,

[Amount in LC_DAY21_VAL] as 21,

[Amount in LC_DAY22_VAL] as 22,

[Amount in LC_DAY23_VAL] as 23,

[Amount in LC_DAY24_VAL] as 24,

[Amount in LC_DAY25_VAL] as 25,

[Amount in LC_DAY26_VAL] as 26,

[Amount in LC_DAY27_VAL] as 27,

[Amount in LC_DAY28_VAL] as 28,

[Amount in LC_DAY29_VAL] as 29,

[Amount in LC_DAY30_VAL] as 30,

[Amount in LC_DAY31_VAL] as 31

FROM

(qvd);

store ZMM_VALUE into E:\DAILY INVENTORY\ZMM_VALUE.qvd(qvd);

DROP Table ZMM_VALUE;

on Next Tab

TempZMM_QTY:

CrossTable(Day, QTY, 3)

LOAD Material_MATNR,

Plant_WERKS,

Month_MONYR,

[1],

[2],

[3],

[4],

[5],

[6],

[7],

[8],

[9],

[10],

[11],

[12],

[13],

[14],

[15],

[16],

[17],

[18],

[19],

[20],

[21],

[22],

[23],

[24],

[25],

[26],

[27],

[28],

[29],

[30],

[31]

FROM

(qvd) where 1=1;

//EXIT Script;

TempZMM_VALUE:

CrossTable(Day, VALUE, 3)

LOAD Material_MATNR,

Plant_WERKS,

Month_MONYR,

[1],

[2],

[3],

[4],

[5],

[6],

[7],

[8],

[9],

[10],

[11],

[12],

[13],

[14],

[15],

[16],

[17],

[18],

[19],

[20],

[21],

[22],

[23],

[24],

[25],

[26],

[27],

[28],

[29],

[30],

[31]

FROM

(qvd) where 1=1;

FInalTab:

MapInventoryQty:

Mapping

LOAD

Plant_WERKS&'_'&Material_MATNR as MapQty,

QTY

Resident TempZMM_QTY;

MapInventoryValue:

Mapping

LOAD

Plant_WERKS&'_'&Material_MATNR as MapValue,

VALUE

Resident TempZMM_VALUE;

ZMM_INVENTORY:

LOAD

Material_MATNR,

Plant_WERKS,

Month_MONYR,

Day,

num(Day,'00')&'/'&right(Month_MONYR,2)&'/'&left(Month_MONYR,4) as Date,

QTY as InventoryQty,

ApplyMap('MapInventoryValue',Plant_WERKS&'_'&Material_MATNR,null()) as InventoryValue

//num(Day,'00') as NumDay

Resident TempZMM_QTY;

Join

LOAD

Material_MATNR,

Plant_WERKS,

Month_MONYR,

Day,

num(Day,'DD')&'/'&right(Month_MONYR,2)&'/'&left(Month_MONYR,4) as Date,

ApplyMap('MapInventoryQty',Plant_WERKS&'_'&Material_MATNR,null()) as InventoryQty,

VALUE as InventoryValue

// num(Day,'00') as NumDay

Resident TempZMM_VALUE;

drop Tables TempZMM_QTY, TempZMM_VALUE;

EXIT Script;

Re: Assigning Separate Day for Quantity and VALUE

sunny_talwar — Wed, 13 Dec 2017 12:11:57 GMT

I don't understand the reason behind doing a mapping table?

Wouldn't there be multiple values for QTY and VALUE based on different Days and MonthYear? Mapping table needs to be unique in order to work properly.... Do you have only one QTY and VALUE based on the combination of Plant_WERKS & Material_MATNR?

Re: Assigning Separate Day for Quantity and VALUE

munnawar — Wed, 13 Dec 2017 12:19:42 GMT

Hi Sunny,

Yes Different Qty and Value on different Day', Month, Year

I Just tried without Mapping also it showing same issue , Plz can u put ur idea without Mapping , I mean Final load for the script,

Thanking You

Re: Assigning Separate Day for Quantity and VALUE

sunny_talwar — Wed, 13 Dec 2017 12:33:40 GMT

See if this works for you

ZMM_QTY:

LOAD Material_MATNR,

Plant_WERKS,

Month_MONYR,

Quantity_DAY1_QTY as 1,

Quantity_DAY2_QTY as 2,

Quantity_DAY3_QTY as 3,

Quantity_DAY4_QTY as 4,

Quantity_DAY5_QTY as 5,

Quantity_DAY6_QTY as 6,

Quantity_DAY7_QTY as 7,

Quantity_DAY8_QTY as 8,

Quantity_DAY9_QTY as 9,

Quantity_DAY10_QTY as 10,

Quantity_DAY11_QTY as 11,

Quantity_DAY12_QTY as 12,

Quantity_DAY13_QTY as 13,

Quantity_DAY14_QTY as 14,

Quantity_DAY15_QTY as 15,

Quantity_DAY16_QTY as 16,

Quantity_DAY17_QTY as 17,

Quantity_DAY18_QTY as 18,

Quantity_DAY19_QTY as 19,

Quantity_DAY20_QTY as 20,

Quantity_DAY21_QTY as 21,

Quantity_DAY22_QTY as 22,

Quantity_DAY23_QTY as 23,

Quantity_DAY24_QTY as 24,

Quantity_DAY25_QTY as 25,

Quantity_DAY26_QTY as 26,

Quantity_DAY27_QTY as 27,

Quantity_DAY28_QTY as 28,

Quantity_DAY29_QTY as 29,

Quantity_DAY30_QTY as 30,

Quantity_DAY31_QTY as 31

FROM (qvd);

STORE ZMM_QTY into E:\DAILY INVENTORY\ZMM_QTY.qvd(qvd);

DROP Table ZMM_QTY;

ZMM_VALUE:

LOAD Material_MATNR,

Plant_WERKS,

Month_MONYR,

[Amount in LC_DAY1_VAL] as 1,

[Amount in LC_DAY2_VAL] as 2,

[Amount in LC_DAY3_VAL] as 3,

[Amount in LC_DAY4_VAL] as 4,

[Amount in LC_DAY5_VAL] as 5,

[Amount in LC_DAY6_VAL] as 6,

[Amount in LC_DAY7_VAL] as 7,

[Amount in LC_DAY8_VAL] as 8,

[Amount in LC_DAY9_VAL] as 9,

[Amount in LC_DAY10_VAL] as 10,

[Amount in LC_DAY11_VAL] as 11,

[Amount in LC_DAY12_VAL] as 12,

[Amount in LC_DAY13_VAL] as 13,

[Amount in LC_DAY14_VAL] as 14,

[Amount in LC_DAY15_VAL] as 15,

[Amount in LC_DAY16_VAL] as 16,

[Amount in LC_DAY17_VAL] as 17,

[Amount in LC_DAY18_VAL] as 18,

[Amount in LC_DAY19_VAL] as 19,

[Amount in LC_DAY20_VAL] as 20,

[Amount in LC_DAY21_VAL] as 21,

[Amount in LC_DAY22_VAL] as 22,

[Amount in LC_DAY23_VAL] as 23,

[Amount in LC_DAY24_VAL] as 24,

[Amount in LC_DAY25_VAL] as 25,

[Amount in LC_DAY26_VAL] as 26,

[Amount in LC_DAY27_VAL] as 27,

[Amount in LC_DAY28_VAL] as 28,

[Amount in LC_DAY29_VAL] as 29,

[Amount in LC_DAY30_VAL] as 30,

[Amount in LC_DAY31_VAL] as 31

FROM (qvd);

STORE ZMM_VALUE into E:\DAILY INVENTORY\ZMM_VALUE.qvd(qvd);

DROP Table ZMM_VALUE;

TempZMM_QTY:

CrossTable(Day, QTY, 3)

LOAD Material_MATNR,

Plant_WERKS,

Month_MONYR,

[1],

[2],

[3],

[4],

[5],

[6],

[7],

[8],

[9],

[10],

[11],

[12],

[13],

[14],

[15],

[16],

[17],

[18],

[19],

[20],

[21],

[22],

[23],

[24],

[25],

[26],

[27],

[28],

[29],

[30],

[31]

FROM (qvd)

Where 1=1;

TempZMM_VALUE:

CrossTable(Day, VALUE, 3)

LOAD Material_MATNR,

Plant_WERKS,

Month_MONYR,

[1],

[2],

[3],

[4],

[5],

[6],

[7],

[8],

[9],

[10],

[11],

[12],

[13],

[14],

[15],

[16],

[17],

[18],

[19],

[20],

[21],

[22],

[23],

[24],

[25],

[26],

[27],

[28],

[29],

[30],

[31]

FROM (qvd)

Where 1=1;

ZMM_INVENTORY_TEMP:

LOAD *

Resident TempZMM_QTY;

Left Join (ZMM_INVENTORY_TEMP)

LOAD *

Resident TempZMM_VALUE;

ZMM_INVENTORY:

LOAD Material_MATNR,

Plant_WERKS,

Month_MONYR,

Day,

Num(Day,'00')&'/'&Right(Month_MONYR,2)&'/'&Left(Month_MONYR,4) as Date,

QTY as InventoryQty,

VALUE as InventoryValue

Resident ZMM_INVENTORY_TEMP;

DROP Tables TempZMM_QTY, TempZMM_VALUE;

EXIT Script;