topic Re: TO MAKE ONE COMMON DATE in QlikView

TO MAKE ONE COMMON DATE

panipat1990 — Mon, 26 Jan 2026 18:19:17 GMT

Dear Team,

i stuck a requirement.i have one table where two date's fields. i want to make them a common date.How is possible.kindly help me for resolving this issue.for this i am sharing data.

In data two field delviery date and doc date. i want to make them one field this is common date(Link Date)

Re: TO MAKE ONE COMMON DATE

el_aprendiz111 — Sat, 25 Mar 2017 13:21:58 GMT

LOAD

Date(Date#(KeepChar([Doc. Date],'0123456789'), 'DDMMYYYY'),'DD/MM/YYYY')AS [Doc. Date],
Date(Date#(KeepChar([Del. Date],'0123456789'), 'DDMMYYYY'),'DD/MM/YYYY')AS [Del. Date],
Purch.Doc.,
Article,
PGr,
Item,
[Net price],
Quantity,
[Net Value],
[GR Qty]
FROM
[..\..\..\..\..\..\..\..\..\Documents\Downloads\Sale.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Re: TO MAKE ONE COMMON DATE

panipat1990 — Sat, 25 Mar 2017 14:12:18 GMT

I want to make them these two date's as a common date

Re: TO MAKE ONE COMMON DATE

vishsaggi — Sat, 25 Mar 2017 15:23:50 GMT

What you mean by common date here? You have DocDate and DelDate having different dates, you want to make DocDate and DelDate have same dates as DocDate or IF you want to combine two dates as One single column which field date should be displayed?

Re: TO MAKE ONE COMMON DATE

el_aprendiz111 — Sat, 25 Mar 2017 15:38:08 GMT

Hi,

LOAD Date(Date#(KeepChar([Doc. Date],'0123456789'), 'DDMMYYYY'),'DD/MM/YYYY')AS [common date],
Purch.Doc.,
Article,
PGr,
Item,
[Net price],
Quantity,
[Net Value],
[GR Qty]
FROM
[..\..\..\..\..\..\..\..\..\Documents\Downloads\Sale.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LOAD Date(Date#(KeepChar([Del. Date],'0123456789'), 'DDMMYYYY'),'DD/MM/YYYY')AS [common date],
Purch.Doc.,
Article,
PGr,
Item,
[Net price],
Quantity,
[Net Value],
[GR Qty]
FROM
[..\..\..\..\..\..\..\..\..\Documents\Downloads\Sale.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

EXIT Script;

Re: TO MAKE ONE COMMON DATE

panipat1990 — Sat, 25 Mar 2017 15:40:38 GMT

Actually i done this way but i want to do another way for solving. suppose my qvd size 3gb.if i concatenate above same.then size increase.this is not optimize way .kindly guide me another way for solving

Re: TO MAKE ONE COMMON DATE

panipat1990 — Sat, 25 Mar 2017 15:42:08 GMT

I want to make one common field common date

Re: TO MAKE ONE COMMON DATE

vishsaggi — Sat, 25 Mar 2017 16:36:48 GMT

What dates needs to be in your CommonDate field?

Re: TO MAKE ONE COMMON DATE

el_aprendiz111 — Sat, 25 Mar 2017 16:56:40 GMT

Hi,

TMP:
LOAD
Date(Date#(KeepChar([Doc. Date],'0123456789'), 'DDMMYYYY'),'DD/MM/YYYY')AS [common date],
Purch.Doc.,
Article,
PGr,
Item,
[Net price],
Quantity,
[Net Value],
[GR Qty]
FROM
[..\..\..\..\..\..\..\..\..\Documents\Downloads\Sale.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LOAD Date(Date#(KeepChar([Del. Date],'0123456789'), 'DDMMYYYY'),'DD/MM/YYYY')AS [common date],
Purch.Doc.,
Article,
PGr,
Item,
[Net price],
Quantity,
[Net Value],
[GR Qty]
FROM
[..\..\..\..\..\..\..\..\..\Documents\Downloads\Sale.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

NoConcatenate

SALES:
LOAD
[common date],
Purch.Doc.,
Article,
PGr,
Item,
sum([Net price]) AS [Net price],
SUM(Quantity)    AS Quantity,
SUM([Net Value]) AS [Net Value],
SUM([GR Qty])    AS [GR Qty]
Resident TMP
Group BY
[common date],
Purch.Doc.,
Article,
PGr,
Item
;

DROP Table TMP;
EXIT Script;

Re: TO MAKE ONE COMMON DATE

panipat1990 — Sun, 26 Mar 2017 04:22:48 GMT

Dear Sir,

Kindly tell me how to identified this is document date and this del date From Common date.

Re: TO MAKE ONE COMMON DATE

HirisH_V7 — Sun, 26 Mar 2017 06:08:16 GMT

Hi,

on what logic you want to combine both dates .can you please post a sample data with your columns and output you want to see.

-Hirish

Re: TO MAKE ONE COMMON DATE

HirisH_V7 — Sun, 26 Mar 2017 06:14:45 GMT

Sorry just checked your sample data,

please tell the logic there at mentioned line in your data & one column you want see for that particular row.

-Hirish

Re: TO MAKE ONE COMMON DATE

panipat1990 — Mon, 27 Mar 2017 02:33:12 GMT

Hi Hirish,

in my data there are two date field's .i want to combine these two fields and document date and delivery date based on flag.

Re: TO MAKE ONE COMMON DATE

vishsaggi — Mon, 27 Mar 2017 03:48:08 GMT

Not clear, can you point out some example dates and let us know what you want in that Flag field?

Re: TO MAKE ONE COMMON DATE

panipat1990 — Mon, 27 Mar 2017 04:49:12 GMT

in above attachment data there are two date's field.document date and del date.i want to combine these two fields and make them one common date which will be associated calendar. i want to GRC QTY del date and documnet date wise.

Re: TO MAKE ONE COMMON DATE

Anonymous — Mon, 27 Mar 2017 11:55:14 GMT

Do you want to make a new selection field called "Common Date" where when you select a date from this field it will show you the records falling under this date either del date or document date?

Re: TO MAKE ONE COMMON DATE

vishsaggi — Mon, 27 Mar 2017 14:19:31 GMT

Can you give a sample output you are expecting here? When you say combine, what exactly you want to combine like you have two different dates in those fields. You want like if dates are equal then 1 else 0 as Flag? Is this what you are expecting?

Re: TO MAKE ONE COMMON DATE

MarcoWedel — Mon, 27 Mar 2017 23:46:58 GMT

Hi,

one solution might be:

SET DateFormat='DD.MM.YYYY';

table1:
LOAD RecNo() as ID,
    [Doc. Date],
    [Del. Date],
    Purch.Doc.,
    Article,
    PGr,
    Item,
    Num#([Net price],'#,##,##,###.##') as [Net price],
    Num#(Quantity,'#,##,##,###.##') as Quantity,
    Num#([Net Value],'#,##,##,###.##') as [Net Value],
    Num#([GR Qty],'#,##,##,###.##') as [GR Qty]
FROM [https://community.qlik.com/servlet/JiveServlet/download/1238474-271615/Sale.txt] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

tabDates:
CrossTable (DateType, Date)
LOAD ID,
    [Doc. Date],
    [Del. Date]
Resident table1;

Re: TO MAKE ONE COMMON DATE

panipat1990 — Thu, 30 Mar 2017 04:36:18 GMT

Dear Marco,

can i merge these two table in one table instead of linking.because my other transaction (Stock) will be concatenate to this .then how will it syn.

Re: TO MAKE ONE COMMON DATE

panipat1990 — Thu, 30 Mar 2017 09:35:44 GMT

It is identified one transaction. if i concatenate another transaction like stock transaction And Scan Transaction And PO Transaction.Then How will make a common date For associated calendar.Kindly help me for solving this.Still be waiting.